In a Google spreadsheet, how can I refer to two different columns in a COUNTIF function?

Question

I'll try my best to explain. Say I've got a sheet that looks like this (in this contrived example, let's say I'm sending marketing emails to various people, I know their ages and zip codes, and I want to track click rates in those emails):

        A   |    B        |    C  
  1     Age |    zip code |  click?  
  2     26  |    11111    |  true  
  3     27  |    11112    |  true  
  4     28  |    11111    |  false  
  5     27  |    22222    |  false  
  6     28  |    11112    |  false  
  7     26  |    22222    |  true  

Now I want to track various stats. In this case, I want to track the click rate by age and by zip code. So I've got a table like this:

      E         F          G 
 1   Age  |  # sent  | # clicked
 2   26   |
 3   26   |
 4   27   |
 5   28   |

So basically in the "# sent" column, I can put (in F2, for example):

=COUNTIF(A:A, E2)

And this would result in a 2 in cell F2, because there were two occurrences of "26" in column A.

But now what if I want to know how many clicked? I want to say something like:

=COUNTIF(A:A == E2  AND  C:C == True, for the same row where A:A matched E2)

I know I can add another column D with a formula like

=IF(C2, A2, "")

Which will only but the age in column D if column C is true. But I can have potentially many criteria and it doesn't seem like I should have to add another "fake" column for each criteria column I have.

Is there a way to do this? If not in Google Docs, in Excel?

Answer 1

Use a combination of the ARRAYFORMULA, SUM and arithmetic operations

Here's the function to count all of the clicks by a 26 year old:

=ARRAYFORMULA(sum((A:A=26) * (C:C="true")))

There are 3 parts to this operation.

• The ARRAYFORMULA takes care of looping over the specified range
• The SUM manages counting all of the true results

Essentially, true is being converted to 1 and false is being converted to 0. Boolean logic is done by using arithmetic operations.

An AND operation uses multiplication:

• (1 * 1) = 1 - (True && True) = True
• (1 * 0) = 0 - (True && False) = False
• (0 * 0) = 0 - (False && False) = False

An OR operation uses a combination of the *SIGN function and addition:

• sign(1 + 1) = 1 - (True || True) = True
• sign(1 + 0) = 1 - (True || False) = True
• sign(0 + 0) = 0 - (False || False) = False

Note: The sign function is necessary because of the way boolean addition works differently than arithmetic addition. Basically in boolean addition 1 + 1 = 1, in arithmetic addition 1 + 1 = 2. Obviously, arithmetic addition will mess up the count so you need to run the results of the addition operations through a sign function. The sign function returns 1 if the value is positive, 0 if the value is 0, and -1 if the value is negative.

Lets say you wanted to count the clicks for all users between age 20-25:

=ARRAYFORMULA(sum(sign((A:A=20) + (A:A=21) + (A:A=22) + (A:A=23) + (A:A=24) + (A:A=25)) * (C:C="true")))

Answer 2

In Excel I would use SUMPRODUCT in Excel 2003 or COUNTIFS (with an "S") in Excel 2007 or later.......but in Google Docs try this

=arrayformula(sum((A:A=E2)*(C:C=TRUE)))

Answer 3

=COUNTA( IFERROR( FILTER('Guest List'!$G:$G ; 'Guest List'!$G:$G = "Yes" ; 'Guest List'!$L:$L = "USA" ) ) )

This was for two columns- I am using this for my wedding guest list- in this case, tabulating "Yes" on sending a save the date, and "USA" for location, so I can calculate postage later. There may be a shorter formula, but this seems to work, so I'm not messing with it!

Happy counting.

Answer 4

In Excel, I would forget formulae, and use Pivot tables.

You might need to add one "counter" column, which could be used over and over as the variable that you sum to get results.