How do I combine COUNTIF with OR

Question

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.

score 41 · Accepted Answer · answered Jan 29 '14 at 11:58

41

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

answered Jan 29 '14 at 11:58

wchiquito

14,738
2
30
42

1

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](https://support.google.com/drive/answer/3094147) – wchiquito Jan 29 '14 at 17:45
5

*NB:* watch out for cases when there are rows matching _both_ criteria; those will be counted twice. – törzsmókus Apr 10 '19 at 16:56

score 7 · Answer 2 · answered Sep 19 '18 at 21:50

7

You can also use ArrayFormula around a SUM(COUNTIFS()) construct:

=ArrayFormula(SUM(COUNTIF(B:B,{"Mammal", "Bird"}))

Source: Google Docs Product Forum

answered Sep 19 '18 at 21:50

nanselm2

957
7
10

ArrayFormula is much better and certainly more maintainable that typing ranges and strings over and over again with COUNTIFS. Thank-you for finding this!!! – MGoBlue93 Apr 28 '21 at 21:54

score 1 · Answer 3 · answered Jul 03 '19 at 18:37

1

you can use regex like this:

=ARRAYFORMULA(SUM(N(REGEXMATCH(B:B, "Mammal|Bird"))))

answered Jul 03 '19 at 18:37

player0

69,261
8
33
67

How do I combine COUNTIF with OR

3 Answers3

Linked