COUNTIF with Multiple Criteria in Google Sheets (AND and OR, Tested)

COUNTIF takes one condition. To count rows that meet two or more, use COUNTIFS for “this and that”, and a different approach for “this or that”. The OR case is where most formulas go wrong: they return a number that looks right and counts some rows twice. This guide shows both, with the result Google Sheets gave for every formula.

The examples use this table of orders in A1:E13:

A: Date B: Rep C: Status D: Item E: Amount
2 2026-01-05 Ana Paid Apple pie 120
3 2026-01-12 Ben Pending Pineapple 80
4 2026-01-20 Ana Pending apple juice 45
5 2026-01-31 18:30 Cara Paid Banana 200
6 2026-02-03 Ben Paid Cherry 60
7 2026-02-14 Ana Overdue Apple* 150
8 2026-02-28 Cara Paid Grape 90
9 2026-03-01 (text) Ana Paid Mango 300
10 2026-03-15 Ben Cancelled Applesauce 75
11 2026-03-20 Cara Pending Kiwi? 110
12 2026-03-31 Ana Paid Pear 100 (text)
13 (empty) Ben Paid Plum 50

Two cells are deliberately stored as text, as happens with imported data: the date in A9 and the amount in E12. They change some results, and we point out where.

Tested in Google Sheets. Every formula on this page was run on November 12, 2026, and the results below are the ones Google Sheets returned. How we test

AND: every condition must be true (COUNTIFS)

COUNTIFS takes pairs of arguments: a range, then the condition for that range. A row is counted only if it meets every condition.

=COUNTIFS(C2:C13, "Paid", B2:B13, "Ana")

This returned 3: rows 2, 9 and 12 are paid orders by Ana. You can keep adding pairs. Paid orders over 100:

=COUNTIFS(C2:C13, "Paid", E2:E13, ">100")

returned 3 (120, 200 and 300). Three conditions, Ana’s orders that aren’t cancelled and are under 200, =COUNTIFS(B2:B13, "Ana", C2:C13, "<>Cancelled", E2:E13, "<200"), also returned 3.

Operators go inside the quotes

You want Condition Our result
Greater than 100 ">100" 5
Between 50 and 100 E2:E13, ">=50", E2:E13, "<=100" 5
Not equal to Paid "<>Paid" 5
Paid, but not Ana C2:C13, "Paid", B2:B13, "<>Ana" 4
Starts with “Pa” "Pa*" 7
Above the average ">"&AVERAGE(E2:E13) 3 for Ana

“Between” is two conditions on the same column, as in the second row. The same trick does not work for “Paid or Pending”: =COUNTIFS(C2:C13, "Paid", C2:C13, "Pending") returned 0, because no cell is both at once.

A value from another cell: join it with &

With 100 in L1, =COUNTIFS(E2:E13, ">"&L1) returned 5. Writing the reference inside the quotes, ">L1", returned 0 with no error: COUNTIFS looked for values greater than the text “L1”.

Upper and lower case don’t matter: "paid" counted the same 7 rows as "Paid". For case-sensitive counts, see COUNTIF contains text.

OR: at least one condition is true

Several values in the same column

To count orders that are Paid or Pending, add two COUNTIFs:

=COUNTIF(C2:C13, "Paid") + COUNTIF(C2:C13, "Pending")

This returned 10 (7 + 3). It’s safe because one cell can’t hold both values. A shorter version puts the values in braces, but it needs ARRAYFORMULA:

Formula Result
=ARRAYFORMULA(SUM(COUNTIF(C2:C13, {"Paid", "Pending"}))) 10
=SUM(COUNTIF(C2:C13, {"Paid", "Pending"})) 7: only Paid was counted

Found in testing: without ARRAYFORMULA, the braces version gave no error. It quietly counted only the first value in the list, 7 instead of 10. The same happened with COUNTIFS: =COUNTIFS(C2:C13, "Paid", B2:B13, {"Ana";"Ben"}) returned 3, Ana’s count alone.

Braces also combine with an AND condition. Ana’s orders that are Paid or Pending:

=ARRAYFORMULA(SUM(COUNTIFS(C2:C13, {"Paid", "Pending"}, B2:B13, "Ana")))

returned 4.

Conditions in different columns: don’t add them

Counting rows where the rep is Ana or the status is Paid looks like the same job, but adding two COUNTIFs gives the wrong answer:

Formula Result
=COUNTIF(B2:B13, "Ana") + COUNTIF(C2:C13, "Paid") 12: wrong
=SUMPRODUCT(SIGN((B2:B13="Ana") + (C2:C13="Paid"))) 9
=COUNTIF(B2:B13, "Ana") + COUNTIF(C2:C13, "Paid") - COUNTIFS(B2:B13, "Ana", C2:C13, "Paid") 9

The sum counted Ana’s three paid orders twice: once as Ana’s and once as paid. 12 is also every row in the table, which makes the mistake easy to miss.

  • SUMPRODUCT with SIGN tests each row. (B2:B13="Ana") + (C2:C13="Paid") gives 0, 1 or 2 per row, and SIGN turns the 2 into 1, so each row counts once.
  • Subtracting the overlap also works, but with three OR conditions you’d need to correct several overlaps. SUMPRODUCT grows more easily: add a third bracket inside SIGN.

Mistakes that return a wrong number

  • Ranges of different sizes. =COUNTIFS(C2:C13, "Paid", B2:B12, "Ana") returned #VALUE! with Array arguments to COUNTIFS are of different size. Make every range start and end on the same rows.
  • Numbers stored as text. The text “100” in E12 was skipped by ">100" and by ">=100": both returned 5. Yet =COUNTIF(E2:E13, 100) returned 1, because an equals condition did match the text “100”. If your counts look off, see convert text to numbers.
  • The cell reference inside the quotes, as shown above: ">L1" counts 0.

Other ways to count with conditions

FILTER returns the matching rows, and ROWS counts them: =ROWS(FILTER(A2:A13, B2:B13="Ana", C2:C13="Paid")) returned 3. It’s handy when you also want to see which rows matched. If nothing matches, FILTER returns #N/A instead of 0, so wrap it in IFNA.

QUERY can count too, but pick the column with care. select count(B) where B='Ana' and C='Paid' returned 3, while count(A) on the same rows returned 2. Column A mixes real dates with one text date, and count(A) left the text date out. Count a column that holds the same type of value in every row, such as the rep names. More QUERY examples are in QUERY ORDER BY multiple columns.

Related guides