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 59 · Accepted Answer · answered Jan 29 '14 at 11:58

59

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

16,177
2
34
45

3

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
6

*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 14 · Answer 2 · answered Sep 19 '18 at 21:50

14

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

1,397
10
11

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 4 · Answer 3 · answered Jul 03 '19 at 18:37

4

you can use regex like this:

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

answered Jul 03 '19 at 18:37

player0

124,011
12
67
124

Frédéric Camblor · Answer 4 · 2022-04-13T22:32:27.027

2

You can use QUERY which can be very powerful (and is generally easier to remember, particularly if you came from the DB world) :

QUERY(A:B;"SELECT COUNT(B) WHERE B='Mammal' OR B='Bird'";0)

(0 at the end is used to avoid including header rows if you have such in your range)

Documentation about the QUERY function here : https://support.google.com/docs/answer/3093343?hl=en

edited Apr 13 '22 at 22:32

answered Jan 17 '22 at 12:52

Frédéric Camblor

1,301
2
12
22

How do I combine COUNTIF with OR

4 Answers4

Linked