CODEWITHSUNDEEP
excelgoogle-sheetscountif
Josh Hyatt
Josh Hyatt

Reputation: 23

How can I refer to two different columns in a COUNTIF function?

I have a sheet that looks something like this.

       A   |    B         |    C  
1     Age  |   how often  |  occupation 
2     21   |   I don't    |  student  
3     22   |x times a week|  photographer  
4     23   |   etc        |  student  
5     22   |   etc        |  builder  
6     21   |   etc        |  car mechanic  
7     20   |   I don't    |  student

I want to track various things, such as the amount of times a student said "I don't". I'm using google spread sheets at the moment. How in google spread sheets can I calculate this?

At the moment this is the query I'm using to try to calculate this.

=ARRAYFORMULA(sum((B2:B7="I don't") * (C2:C7="student")))

All results are coming up as zero - cannot seem to get a result. If anyone could help it would be much appreciated.

Have also tried below with no luck. 

=SUM(IF(B2:B7="I don't",IF(C2:C7="student",1,0)))

Any help would be greatly appreciated. Thank you very much.

Upvotes: 1

Views: 66

Answers (3)

Jake Bourne
Jake Bourne

Reputation: 763

You have a few options to present this information, I'll talk to countifs and pivots:

To count the amount of occurrences a text has appeared in a range of cells you'll use a formula like =COUNTIFS(B2:B7, "I don't", C2:C7, "student") however, if you're looking to see how many the string "I don't" appears, ie.. "I don't know" or "I don't care for your formula" enter in as an array selecting crtl + Shift + Enter. How to Count the Occurrences of a Text String

But. For your case, which I'm guessing is interpreting the data and drawing comparisons, such as counting students vs builders, age vs occurrences, you're better off using a pivot table. This will pull together all the information and provide an automatic sum or count on the criteria set, quickly drawing comparisons. Google has a great how to guide you can refer off for google sheets Google Pivot Table

Upvotes: 0

Amit
Amit

Reputation: 46331

In google-spreadsheets, as well as in Excel, you can use the COUNTIFS() (Google, Excel) function:

=COUNTIFS(B2:B7, "I don't", C2:C7, "student")

Upvotes: 2

JPV
JPV

Reputation: 27262

In a google spreadsheet you can also use SUMPRODUCT()

=sumproduct(B2:B7="I don't", C2:C7="student")

Upvotes: 0

Related Questions