Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

share|improve this question

migrated from superuser.com Jan 17 '12 at 6:23

This question came from our site for computer enthusiasts and power users.

5 Answers 5

up vote 8 down vote accepted

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")))
share|improve this answer
    
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))) –  barry houdini Jan 18 '12 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. –  Evan Plaice Jan 19 '12 at 6:32
    
Apologies, Evan, I misunderstood - that's an excellent example. –  barry houdini Jan 19 '12 at 19:55
    
you can avoid =true, so: =ARRAYFORMULA(sum((A:A=26)*C:C)) –  tic Feb 2 '12 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. –  Evan Plaice Feb 2 '12 at 22:29

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

share|improve this answer
=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.

share|improve this answer

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.

share|improve this answer

As of now, the new Google Sheets supports COUNTIFS, which can directly handle the required job.

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

List out all the ranges and the comparison to make separated by commas.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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