CODEWITHSUNDEEP
google-sheetsformulas
Eric A. Meyer
Eric A. Meyer

Reputation: 930

Google Sheets counting with multiple criteria

I have a Google Sheet where I have several columns of data, and I want to get a count of how many rows match two criteria, where one of the criteria is matching either one of two values.

Here’s an example of the data I have:

enter image description here

What I want to do is things like: get a count of how many rows have “Yes” in column A, and either “A” or “C” in column B. Or how many rows are “No” and either “I” or “X”.

I’ve come up with this:

=COUNTIFS($A1:$A21,"Yes",B1:B21,"="&"A")+COUNTIFS($A1:$A21,"Yes",B1:B21,"="&"C")

…but that feels clunky, and makes it harder to update if I decide to shift columns around. Not to mention really bad if I want to combine multiple bits of information into a single cell, such as this:

=(COUNTIFS($A1:$A21,"Yes",B1:B21,"="&"A")+COUNTIFS($A1:$A21,"Yes",B1:B21,"="&"C")) & "/" & (COUNTIFS($A1:$A21,"No",B1:B21,"="&"A")+COUNTIFS($A1:$A21,"No",B1:B21,"="&"C"))

I mean, that’s just awful. It works, but it’s awful.

I’ve tried using OR() without success, and also tried curly-bracket syntax without success. I fully acknowledge I may have done both of them wrong, but if so, darned if I can figure out what I missed. Any Sheets mavens willing to take pity on an old dude and show me a much smarter way to do this?

Upvotes: 1

Views: 4001

Answers (5)

user6655984
user6655984

Reputation:

For complex logic, use more powerful commands like query or filter.

count the rows with “Yes” in column A, and either “A” or “C” in column B.

becomes

=query(A:C, "select count(A) where A='Yes' and (B='A' or B='C')")

or

=query(A:C, "select count(A) where A='Yes' and (B='A' or B='C') label count(A) ''")

if you don't want to have a column header such as "count".

This is pretty much stating the goal in English (well, SQL version of it).

Upvotes: 1

TheMaster
TheMaster

Reputation: 50382

Shortest one so far: 

=SUMPRODUCT(REGEXMATCH(A1:A8&B1:B8,"(?i)Yes(A|C)"))

CONCATENATE both columns using & and use REGEX on the result.

  • (?i) Case insensitive
  • yes(A|C) yes followed by A or C
  • SUM up all the trues.

For a complex condition, 

=ARRAYFORMULA(SUM(--REGEXMATCH(A1:A8&B1:B8,"(?i)yes(A|C)"))&"/"&SUM(--REGEXMATCH(A1:A8&B1:B8,"(?i)no(I|X)")))

Note that there should be no trailing spaces following yes/No and no leading spaces before A or C etc. If there are, use TRIM.

Upvotes: 3

Ed Nelson
Ed Nelson

Reputation: 10259

I would use query with variables. In F1 put:

=query(A:B,"select count(A) where A ='"&C2&"' AND B='"&D2&"' OR A ='"&C2&"' AND B='"&E2&"'")

In C2 enter "Yes" or "No" and in D2 and E2 enter the B letters (or leave blank). Enter whatever headers you want in C1, D1, and E1.

Upvotes: 2

Ken Butler
Ken Butler

Reputation: 55

Simplify:

  • Create a column C that is TRUE if B is A or C, FALSE otherwise.

    Create a column D that is TRUE if B is I or X, FALSE otherwise.

    Create a column E that is TRUE if A is "yes" and C is TRUE.

    Create a column F that is TRUE if A is "no" and D is TRUE

    Create a column G that is column E or column F.

    Sum up the values in column G.

Upvotes: -1

a3k4
a3k4

Reputation: 11

I'm not sure if this is exactly what you're looking for, but you could simplify it a bit by just creating a "pairings" list.

E2: =COUNTIFS(A:A,$C2,B:B,$D2)

E3: =COUNTIFS(A:A,$C3,B:B,$D3)

...

E6: =SUM(E2:E5)

The benefit is that it's flexible - you can add as many pairings as you want later on. Also, no complexity of array formulas.

Upvotes: 1

Related Questions