Can COUNTIFS compare values in two different columns (like On Hand vs. Reorder Point)?

No, COUNTIFS only compares each range against a fixed criterion. To count rows where one column is less than another, use SUMPRODUCT instead: =SUMPRODUCT((C2:C9<D2:D9)*1).

How do I use OR logic instead of AND with COUNTIFS?

COUNTIFS requires all criteria to be true (AND logic). For OR logic, use separate COUNTIF functions: =COUNTIF(B:B,"A")+COUNTIF(B:B,"B") counts items in Warehouse A or B. Alternatively, use SUMPRODUCT: =SUMPRODUCT(((B2:B9="A")+(B2:B9="B"))*1).

What happens if my criteria_range and criteria are mismatched in size?

If ranges are different sizes, COUNTIFS may return #VALUE! or unpredictable results. Always use ranges of equal size (e.g., B2:B9 with a single criterion for all 8 rows). Type mismatches usually trigger #VALUE! errors.

Can I use date ranges as criteria in COUNTIFS?

Yes, compare dates using operators and the DATE function: =COUNTIFS(D2:D9,">="&DATE(2025,1,1),D2:D9,"<"&DATE(2025,12,31)) counts entries within a specific year. Ensure your range contains actual date values, not text.

COUNTIFS function

Q: How do I use OR logic instead of AND with COUNTIFS?

COUNTIFS requires all criteria to be true (AND logic). For OR logic, use separate COUNTIF functions: =COUNTIF(B:B,"A")+COUNTIF(B:B,"B") counts items in Warehouse A or B. Alternatively, use SUMPRODUCT: =SUMPRODUCT(((B2:B9="A")+(B2:B9="B"))*1).

Q: What happens if my criteria_range and criteria are mismatched in size?

If ranges are different sizes, COUNTIFS may return #VALUE! or unpredictable results. Always use ranges of equal size (e.g., B2:B9 with a single criterion for all 8 rows). Type mismatches usually trigger #VALUE! errors.

Q: Can I use date ranges as criteria in COUNTIFS?

Yes, compare dates using operators and the DATE function: =COUNTIFS(D2:D9,">="&DATE(2025,1,1),D2:D9,"<"&DATE(2025,12,31)) counts entries within a specific year. Ensure your range contains actual date values, not text.

COUNTIFS counts rows where all criteria are met across multiple ranges, useful for filtering inventory and finding matches across conditions.

=COUNTIFS(criteria_range1, criteria1, ...)

Generate a COUNTIFS formula

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

Arguments

How COUNTIFS reads its arguments

Argument	Required	Description
criteria_range1	Required	The first range to evaluate against a criterion (text, numbers, or dates). Can be a column, row, or array. Required.
criteria1	Required	The condition to test against criteria_range1, such as ">50", "Warehouse A", or a cell reference. Required.
...	Repeating	Additional pairs of criteria_range and criteria arguments. COUNTIFS supports up to 127 range/criteria pairs, allowing simultaneous evaluation of multiple conditions.

Returns

An integer representing the count of rows that satisfy all specified criteria.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Count low-stock items in Warehouse A

SKU	Warehouse	On Hand	Reorder Point	Cost
SKU001	A	150	50	25
SKU001	B	85	50	25
SKU002	A	200	100	15.5
SKU002	B	40	100	15.5
SKU003	A	120	75	30
SKU003	B	55	75	30
SKU004	A	30	50	12
SKU004	B	95	50	12

=COUNTIFS(B2:B9,"A",C2:C9,"<50")

Result: 1

COUNTIFS finds rows where Warehouse equals "A" AND On Hand is less than 50. Only SKU004 in Warehouse A (30 units) meets both conditions, returning 1.

2. Count items above a cost threshold

SKU	Warehouse	On Hand	Reorder Point	Cost
SKU001	A	150	50	25
SKU001	B	85	50	25
SKU002	A	200	100	15.5
SKU002	B	40	100	15.5
SKU003	A	120	75	30
SKU003	B	55	75	30
SKU004	A	30	50	12
SKU004	B	95	50	12

=COUNTIFS(E2:E9,">=25")

Result: 4

COUNTIFS counts all rows where Cost is greater than or equal to 25. Four items meet this threshold: both SKU001 units at $25 and both SKU003 units at $30.

3. Count premium items in Warehouse B

SKU	Warehouse	On Hand	Reorder Point	Cost
SKU001	A	150	50	25
SKU001	B	85	50	25
SKU002	A	200	100	15.5
SKU002	B	40	100	15.5
SKU003	A	120	75	30
SKU003	B	55	75	30
SKU004	A	30	50	12
SKU004	B	95	50	12

=COUNTIFS(B2:B9,"B",E2:E9,">20")

Result: 2

COUNTIFS counts rows where Warehouse is "B" AND Cost is greater than 20. SKU001-B ($25) and SKU003-B ($30) both qualify, while SKU002-B ($15.50) and SKU004-B ($12) do not exceed the threshold.

Common errors

Which COUNTIFS error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	Criteria syntax is invalid or mismatched with range data types, such as using unquoted text, malformed operators, or type coercion errors.	Wrap text criteria in quotes (e.g., "Warehouse A"), use valid operators (>, <, =, >=, <=, <>), and ensure proper syntax like "<50" not <50 for numeric comparisons.
#REF!	A criteria_range reference points to cells that have been deleted, moved, or otherwise made invalid.	Verify that all range references (B2:B9, E2:E9, etc.) point to existing data. If rows were deleted, update the range boundaries accordingly.
#NAME?	The function name is misspelled (e.g., COUNTIF instead of COUNTIFS) or a named range used in criteria does not exist.	Double-check the function spelling is exactly COUNTIFS. If using named ranges in criteria, confirm they are defined in the spreadsheet's name management dialog.

Tips and when to use something else

Use COUNTIF when you need only one criterion; it is simpler and slightly more efficient than COUNTIFS.
Text criteria support wildcards: * matches any sequence of characters and ? matches a single character. Example: =COUNTIFS(A:A,"SKU*") counts rows where column A starts with SKU.
Combine COUNTIFS with other functions to analyze filtered subsets. For example, =SUMIFS(E2:E9,B2:B9,"A",C2:C9,">100") sums costs for well-stocked items in Warehouse A.
Always pair criteria_range and criteria in the correct order: each range must have exactly one corresponding criterion, or the formula returns an error.

Related functions

COUNTIFCounts cells in a range that meet a specific criterion, returning a number — use it to tally matching rows like employee counts by department.SUMIFSSUMIFS returns the sum of cells that meet multiple criteria, making it ideal for aggregating data with several conditions at once.AVERAGEIFSAVERAGEIFS returns the average of values in a range that meet multiple criteria, enabling filtered averages across complex datasets.MAXIFSReturns the largest value in a range that meets multiple criteria, combining the power of MAX with IF-like filtering.MINIFSReturns the minimum value in a range that meets one or more specified criteria, useful for finding smallest quantities subject to filters.

Frequently asked questions

Can COUNTIFS compare values in two different columns (like On Hand vs. Reorder Point)?: No, COUNTIFS only compares each range against a fixed criterion. To count rows where one column is less than another, use SUMPRODUCT instead: =SUMPRODUCT((C2:C9<D2:D9)*1).
How do I use OR logic instead of AND with COUNTIFS?: COUNTIFS requires all criteria to be true (AND logic). For OR logic, use separate COUNTIF functions: =COUNTIF(B:B,"A")+COUNTIF(B:B,"B") counts items in Warehouse A or B. Alternatively, use SUMPRODUCT: =SUMPRODUCT(((B2:B9="A")+(B2:B9="B"))*1).
What happens if my criteria_range and criteria are mismatched in size?: If ranges are different sizes, COUNTIFS may return #VALUE! or unpredictable results. Always use ranges of equal size (e.g., B2:B9 with a single criterion for all 8 rows). Type mismatches usually trigger #VALUE! errors.
Can I use date ranges as criteria in COUNTIFS?: Yes, compare dates using operators and the DATE function: =COUNTIFS(D2:D9,">="&DATE(2025,1,1),D2:D9,"<"&DATE(2025,12,31)) counts entries within a specific year. Ensure your range contains actual date values, not text.

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