Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to use COUNTIFS with OR logic in Excel and Google Sheets
Topic:COUNTIF & COUNTIFSExcel & Google Sheets
=COUNTIF(A2:A5,"Pending")+COUNTIF(A2:A5,"Review")✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Status |
| Pending |
| Approved |
| Review |
| Pending |
=COUNTIF(A2:A5,"Pending")+COUNTIF(A2:A5,"Review")→3
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Identify the values you want to count as alternatives (OR conditions), e.g., 'Pending' or 'Review'.
- 2Write a separate COUNTIF for each value: =COUNTIF(A2:A100,"Pending")+COUNTIF(A2:A100,"Review").
- 3Sum the results — Excel adds them together so any row matching either value is counted.
- 4If a row could match multiple criteria simultaneously and you don't want to double-count, use SUMPRODUCT with Boolean OR: =SUMPRODUCT(((A2:A100="Pending")+(A2:A100="Review")>0)*1).
Tips
- When counting more than three OR values, list them in a range and use =SUMPRODUCT(COUNTIF(A2:A100,D1:D5)) where D1:D5 holds your values.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
Why doesn't COUNTIFS work for OR logic directly?
COUNTIFS requires ALL conditions to be true simultaneously (AND), so each criteria_range/criteria pair is an additional filter, not an alternative.
Can I combine OR logic with AND logic?
Yes — use SUMPRODUCT: =SUMPRODUCT(((A2:A100="Pending")+(A2:A100="Review")>0)*(B2:B100="Region1")) counts rows in Region1 with either Pending or Review status.
More on COUNTIF & COUNTIFS
See all →Reference
COUNTIF
Count the cells in a range that meet a condition.
How-toCOUNTIF vs COUNTIFS: how they differ in Excel and Sheets
Learn the difference between COUNTIF (single condition) and COUNTIFS (multiple conditions) for counting in Excel and Google Sheets.
Error fixFix COUNTIF
COUNTIF not working in Excel and Google Sheets — fix criteria syntax, data-type mismatches, wildcard errors, and case-sensitivity misunderstandings.
ReferenceCOUNTIFS
Count cells that meet several conditions simultaneously.
How-toHow to count cells not equal to a value
Use COUNTIF with the <> operator to count all cells that do not match a specific value.
Error fixFix Wrong count
COUNTIF returns the wrong count in Excel and Google Sheets — fix wildcard over-matching, duplicate rows, hidden rows, blank cell handling, and date format issues.