Master COUNTIF & COUNTIFS in Excel & Google Sheets

COUNTIF and COUNTIFS answer one simple question: how many cells match the rule? This guide covers exact text, numbers, dates, checkboxes, wildcards, duplicates, blanks, errors, and safer multi-condition counting.

1. The Fundamentals of COUNTIF

COUNTIF scans a specific range of cells and counts only the ones that meet a rule you define. Unlike SUM, it doesn't add values together—it just counts how many cells match your criteria.

=COUNTIF(range, criteria)

• range: The grid of cells to look at (e.g., A2:A100)
• criteria: What you are looking for. Crucial rule: Text values and comparison operators must be wrapped in quotes. Plain numbers and TRUE/FALSE do not need quotes.

2. Exact Text Matching (Not Case-Sensitive)

The most common use case: How many times does a specific text value appear in a column? COUNTIF treats "apple", "Apple", and "APPLE" as the same match.

3. Counting Numbers with Operators

When checking numbers, you must place the comparison operators (>, <, >=, <=, <>) inside the quotes along with the number.

Notice that Finn's score of exactly 50 is not counted. If you want to include 50, you must use ">=50".

Not Equal, Zero, Positive, and Negative Counts

These are small examples, but they are incredibly common in daily workflows.

4. Connecting to Dynamic Cells (The Ampersand)

Hardcoding numbers like ">50" into your formulas is bad practice. If your target changes to 60, you have to rewrite the formula. Instead, put your target number in a cell (like D1) and use the & symbol to glue the operator to the cell reference.

=COUNTIF(B2:B100, ">" & D1)

Now, whenever you type a new number into D1, your COUNTIF updates instantly.

Dynamic Text Criteria

The same ampersand trick works with text and wildcards too. If cell D1 contains the text son, this formula dynamically counts any cell containing those letters.

=COUNTIF(A2:A100, "*" & D1 & "*")

5. Counting Checkboxes (Booleans)

In Google Sheets and modern Excel, checkboxes are just visual masks for TRUE (checked) and FALSE (unchecked). You do not use quotes around boolean logic.

6. Advanced: Wildcards and Exact Lengths

Wildcards turn COUNTIF into a powerful text-search engine:

• * (Asterisk) matches any number of characters.
• ? (Question Mark) matches exactly one character.

Escaping Wildcards (The Tilde Method)

What if you actually need to count cells that literally contain an asterisk (*) or a question mark (?) in the text? Because Excel sees them as wildcards, a formula like =COUNTIF(A:A, "*") will simply count *all text cells*. To count any non-blank value, use =COUNTIF(A:A, "<>").

To tell Excel to look for the literal character, place a tilde (~) in front of it:

=COUNTIF(A:A, "*~**")
=COUNTIF(A:A, "*~?*")
=COUNTIF(A:A, "*~~*")

The first formula counts cells containing a literal asterisk, the second counts a literal question mark, and the third counts a literal tilde.

7. Counting Dates and Times Correctly

Dates are stored as numbers in Excel and Google Sheets, so COUNTIF can count them. The safest method is to use DATE() instead of typing dates as text, because text dates can behave unpredictably depending on regional settings.

8. COUNTIFS: Handling Multiple Criteria

COUNTIF handles one condition. If you need two or more conditions to be met at the same time (AND logic), you add an "S" to the function.

=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)

9. Advanced: "OR" Logic

COUNTIFS strictly enforces "AND" logic (Apple AND West). But what if you want to count cells that are "Apple" OR "Banana"? There are two ways to do this.

Method 1: The Simple Addition

Just use two separate COUNTIF formulas and add them together.

=COUNTIF(A:A, "Apple") + COUNTIF(A:A, "Banana")

Method 2: The Array Formula (Cleaner)

If you have many OR conditions, adding them gets messy. Pass an array of values into a single COUNTIF, and wrap it in SUM to tally the total.

=SUM(COUNTIF(A:A, {"Apple", "Banana", "Orange"}))

Note: This exact-list OR pattern works directly in modern Excel and Google Sheets. In older Excel, confirm it as an array formula with Ctrl+Shift+Enter. If your regional settings use semicolons instead of commas, adjust the separators.

10. Advanced: Case-Sensitive Counting

Because COUNTIF completely ignores letter casing, it cannot differentiate between "apple" and "APPLE". To count case-sensitively, you must abandon COUNTIF entirely and use a combination of SUMPRODUCT and EXACT.

=SUMPRODUCT(--EXACT(A2:A100, "APPLE"))

How it works: The EXACT function compares the cells to "APPLE" and generates an array of TRUE/FALSE. The double negative (--) turns those booleans into 1s and 0s, and SUMPRODUCT adds up all the 1s.

11. Counting Blanks, Text, Numbers, and Errors

Data cleaning often requires finding empty cells or broken formulas.

• Blank cells or formulas returning empty text: =COUNTIF(A:A, "")
• Cells with any data: =COUNTIF(A:A, "<>")
• Cells containing text: =COUNTIF(A:A, "*")
• Cells containing numbers: =COUNT(A:A) *(COUNTIF is not needed here)*
• #N/A errors only: =SUMPRODUCT(--ISNA(A2:A100))
• #DIV/0! errors only: =SUMPRODUCT(--(IFERROR(ERROR.TYPE(A2:A100),0)=2))
• Any error type: =SUMPRODUCT(--ISERROR(A2:A100))

12. Expert Level: The COUNTIF Tricks People Miss

These are not beginner examples, but they elevate the guide to a truly professional resource.

Quick Cheat Sheet