Skip to main content

Sum multiple countif

17
In Google Spreadsheets, I need to use the COUNTIF function on a range with multiple criteria. So in the table below, I would need to have something like =COUNTIF(B:B,"Mammal"or"Bird") and return a value of 4.
A         |B
-------------------
Animal    | Type
-------------------
Dog       | Mammal
Cat       | Mammal
Lizard    | Reptile
Snake     | Reptile
Alligator | Reptile
Dove      | Bird
Chicken   | Bird
I've tried a lot of different approaches with no luck.
One option:
=COUNTIF(B:B; "Mammal") + COUNTIF(B:B; "Bird")
According to the documentation:
Notes
COUNTIF can only perform conditional counts with a single criterion. To use multiple criteria, use COUNTIFS or the database functions DCOUNT or DCOUNTA.
COUNTIFS: This function is only available in the new Google Sheets.
Example:
=DCOUNTA(B:B; 2; {"Type"; "Mammal"; "Bird"})
improve this answer
  • Thanks wchiquito - DCOUNTA works perfectly. I am curious what the "2" in that formula does exactly. The formula seems to work as expected regardless of what that number is, so long as it's equal to or greater than 1. – VivaNOLA Jan 29 '14 at 15:36
  • @VivaNOLA: Index indicating which column to consider. See DCOUNTA – wchiquito Jan 29 '14 at 17:45
  • 2
    NB: watch out for cases when there are rows matching both criteria; those will be counted twice. – törzsmókus Apr 10 at 16:56
5
You can also use ArrayFormula around a SUM(COUNTIFS()) construct:
=ArrayFormula(SUM(COUNTIF(B:B,{"Mammal", "Bird"}))

1
you can use regex like this:
=ARRAYFORMULA(SUM(N(REGEXMATCH(B:B, "Mammal|Bird"))))
0

Comments

Post a Comment