13

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?

2
  • 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. Jan 17, 2012 at 10:49
  • Note: the answer for new Google Sheets is down there.
    – user79865
    Jul 22, 2015 at 16:42

6 Answers 6

10

Use a combination of the ARRAYFORMULA, SUM and arithmetic operations

Use the approach provided in barry houdini's answer. 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")))
5
  • SIGN isn't required here is it? no single cell can be both 20 and 23, for instance so the addition would never give you a value > 1 in this case. SIGN is only required if the conditions are not mutually exclusive. Also Wouldn't between 20 and 25 more easily be done with this version? =arrayformula(sum((A:A>=20)*(A:A<=25)*(C:C=TRUE))) Jan 18, 2012 at 12:53
  • I was attempting to explain the general use of OR logic. If the tests were not exclusive (contained in the same column) then the SIGN function would be necessary. In this case it isn't. And, yes your example would be the better approach but doesn't demonstrate the use of OR. I was trying to think of a simplistic approach to demonstrate the use of OR that fit in with the OP's question but I'm obviously not very good at coming up with examples off the cuff. If you have some better examples, I encourage you to edit/improve the answer. Jan 19, 2012 at 6:32
  • Apologies, Evan, I misunderstood - that's an excellent example. Jan 19, 2012 at 19:55
  • 1
    you can avoid =true, so: =ARRAYFORMULA(sum((A:A=26)*C:C))
    – tic
    Feb 2, 2012 at 21:27
  • 1
    @tic I wasn't using the boolean true (which is TRUE in Google Spreadsheets) I was referring to 'true' the string that the op used in his question. Try it, I'm pretty sure that if you test the values for C:C it will always return TRUE on a non-empty string. In the spreadsheet cells Google sticks to the Excel rules over JavaScript. Welcome to the fun world of leaky abstractions. Feb 2, 2012 at 22:29
17

Google Sheets now includes the COUNTIFS function which can directly handle the required job.

=COUNTIFS(A:A, E2, C:C, true)

Syntax

=COUNTIFS(
  criteria_range1, criterion1,
  [criteria_range2, criterion2, 
  ...])

You can include as many criteria_range, criterion pairs as you need.

1
  • 5
    Thanks for rewarding me scrolling all the way down. This is the answer when working with the new google sheets. Dec 27, 2014 at 14:43
3

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)))

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.

2

You can add the array that you want to test into {}.

In the example below:

=countif({F2,H2,J2,L2,N2,P2,R2,T2,V2,W2},"TRUE")
0

You can use QUERY to return all results:

=QUERY({A:B, ARRAYFORMULA(C:C*1)},
   "SELECT Col1, COUNT(Col2), SUM(Col3)
    WHERE Col1 IS NOT NULL GROUP BY Col1
    LABEL COUNT(Col2) 'Sent', 
    SUM(Col3) 'Clicked'", 1)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.