Can COUNTIF count cells across multiple separate ranges at once?

No, COUNTIF accepts only one range argument. To count matches across multiple ranges, either use COUNTIFS if they're contiguous, or combine multiple COUNTIF formulas with addition: =COUNTIF(A1:A10,"Sales")+COUNTIF(C1:C10,"Sales").

How do I count cells that don't match a criterion?

Use the not-equal operator: =COUNTIF(range,"<>value"). For example, =COUNTIF(F2:F9,"<>Inactive") counts all cells not equal to 'Inactive', returning 7 for the active employees in our dataset.

What's the difference between COUNTIF and COUNTIFS?

COUNTIF handles one range with one criterion. COUNTIFS handles multiple ranges paired with multiple criteria, allowing complex tallies—for example, =COUNTIFS(C2:C9,"Sales",E2:E9,">80000") counts Sales employees earning over $80,000.

Does COUNTIF work with dates?

Yes. Use comparison operators: =COUNTIF(range,">="&DATE(2022,1,1)) or =COUNTIF(hire_date_range,">"&TODAY()-365) for dates after a specific date or within the past year. Dates work with , =, = operators.

COUNTIF function

Counts cells in a range that meet a specific criterion, returning a number — use it to tally matching rows like employee counts by department.

=COUNTIF(range, criteria)

Generate a COUNTIF formula

Describe what you need. The generator will reach for COUNTIF where COUNTIF is the right tool, and tell you when it is not.

Arguments

How COUNTIF reads its arguments

Argument	Required	Description
range	Required	The cells to evaluate; can span a single column, row, or rectangular block. Non-matching data types and empty cells are ignored unless explicitly matched.
criteria	Required	A value, logical expression, or pattern (using wildcards * for sequences and ? for single characters) to match against. Can be a text string, number, or reference to a cell containing the criterion.

Returns

An integer representing the count of cells matching the criterion.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Count employees in the Sales department

Name	Department
Alice Chen	Sales
Bob Martinez	Engineering
Carol Johnson	Engineering
Diana Kim	Sales
Evan Wright	HR
Fiona Patel	Engineering
George Brown	Sales
Hannah Lee	Finance

=COUNTIF(C2:C9,"Sales")

Result: 3

The formula checks each cell in the range C2:C9 (Department column) and counts those exactly matching the text 'Sales'. Three employees work in Sales: Alice Chen, Diana Kim, and George Brown.

2. Count employees earning over $80,000

Name	Salary
Alice Chen	65000
Bob Martinez	95000
Carol Johnson	88000
Diana Kim	72000
Evan Wright	58000
Fiona Patel	92000
George Brown	61000
Hannah Lee	70000

=COUNTIF(E2:E9,">80000")

Result: 3

The formula uses a numerical comparison operator within the criteria. It counts values in E2:E9 (Salary column) that exceed 80000: Bob Martinez ($95,000), Carol Johnson ($88,000), and Fiona Patel ($92,000).

3. Count employees with Active status

Name	Status
Alice Chen	Active
Bob Martinez	Active
Carol Johnson	Active
Diana Kim	Active
Evan Wright	Inactive
Fiona Patel	Active
George Brown	Active
Hannah Lee	Active

=COUNTIF(F2:F9,"Active")

Result: 7

The formula counts all cells in F2:F9 (Status column) that equal 'Active'. Seven of eight employees have Active status; only Evan Wright is Inactive. COUNTIF is case-insensitive, so 'active', 'ACTIVE', and 'Active' all match identically.

Common errors

Which COUNTIF error are you seeing?

Error	Why it happens	How to fix it
#NAME?	Function name is misspelled (e.g., =CUNTIF or =COUNT_IF) or COUNTIF is not recognized in your spreadsheet application version.	Verify correct spelling: COUNTIF with no underscores, spaces, or typos. Ensure your Excel or Google Sheets version supports this function; it's available in all modern spreadsheet applications.
#VALUE!	Criteria contains invalid syntax—most commonly =COUNTIF(A1:A10,>80000) without quotes around the operator, or incompatible type comparisons (comparing text to numbers).	Quote the entire criteria string: =COUNTIF(A1:A10,">80000"). For cell references, use concatenation: =COUNTIF(A1:A10,">"&A11). Operator and value must be together within quotes.
#REF!	The range references deleted or moved rows/columns, or a cell referenced in criteria exists in a closed workbook or has been removed.	Verify the range boundaries exist and haven't been deleted. Check that any cells referenced in the criteria are in the same open workbook and haven't been removed or moved.

Tips and when to use something else

Use wildcards for flexible text matching: * matches any sequence of characters and ? matches any single character. For example, =COUNTIF(C:C,"Sales*") counts all cells starting with 'Sales', useful for department variants.
For multiple criteria, use COUNTIFS instead—it accepts multiple range-criteria pairs, allowing you to count rows matching several conditions simultaneously.
COUNTIF is case-insensitive: 'Active', 'active', and 'ACTIVE' all match identically, so capitalization differences won't cause miscounts in your results.
To count empty cells, use COUNTBLANK; to count non-empty cells regardless of content, use COUNTA. Both are simpler and faster than COUNTIF alternatives for these specific tasks.

Related functions

COUNTIFSCOUNTIFS counts rows where all criteria are met across multiple ranges, useful for filtering inventory and finding matches across conditions.COUNTBLANKCOUNTBLANK returns the number of empty cells in a specified range, useful for identifying incomplete data or tracking active subscriptions.AVERAGEIFReturns the average of cells matching a single criterion, useful for conditional averages like mean cost by warehouse or mean inventory per SKU.COUNTACOUNTA counts the number of non-empty cells in a range, regardless of their content type or whether they contain text, numbers, or errors.COUNTCOUNT counts the number of cells containing numeric values and dates in one or more ranges, ignoring text and blank cells.

Frequently asked questions

Can COUNTIF count cells across multiple separate ranges at once?: No, COUNTIF accepts only one range argument. To count matches across multiple ranges, either use COUNTIFS if they're contiguous, or combine multiple COUNTIF formulas with addition: =COUNTIF(A1:A10,"Sales")+COUNTIF(C1:C10,"Sales").
How do I count cells that don't match a criterion?: Use the not-equal operator: =COUNTIF(range,"<>value"). For example, =COUNTIF(F2:F9,"<>Inactive") counts all cells not equal to 'Inactive', returning 7 for the active employees in our dataset.
What's the difference between COUNTIF and COUNTIFS?: COUNTIF handles one range with one criterion. COUNTIFS handles multiple ranges paired with multiple criteria, allowing complex tallies—for example, =COUNTIFS(C2:C9,"Sales",E2:E9,">80000") counts Sales employees earning over $80,000.
Does COUNTIF work with dates?: Yes. Use comparison operators: =COUNTIF(range,">="&DATE(2022,1,1)) or =COUNTIF(hire_date_range,">"&TODAY()-365) for dates after a specific date or within the past year. Dates work with <, >, =, <=, >= operators.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17