COUNTIFS
Counts cells that meet multiple criteria. More powerful than COUNTIF.
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)Arguments
criteria_range1First range to evaluatecriteria1First conditioncriteria_range2Additional range(optional)criteria2Additional condition(optional)
=COUNTIFS(A:A,"Sales",B:B,">1000")Counts where A is 'Sales' AND B>1000
=COUNTIFS(A:A,A1,B:B,B1)Counts matching pairs
=COUNTIFS(A:A,">="&DATE(2024,1,1),A:A,"<="&DATE(2024,12,31))Counts dates in 2024
- •All criteria must be met (AND logic)
- •Use with date ranges for date counting
- •Wildcards * and ? work in text criteria
- •Letting the criteria ranges end up different sizes, which trips a #VALUE! error with no obvious clue why
- •Expecting COUNTIFS to give you OR logic between conditions - it's AND-only, every pair has to match at once
- •Writing >=A1 instead of ">="&A1 and wondering why the operator combined with a cell reference doesn't work
COUNTIFThe simpler version when you only need one condition, not severalSUMIFSSame multi-condition structure, just adding up a range instead of counting rowsAVERAGEIFSSame logic once more, this time averaging the matching rows instead of counting themCan COUNTIFS check conditions spread across different columns?
That's exactly what it's for - each criteria_range/criteria pair can point at a different column, and COUNTIFS only counts a row when every pair matches.
How do I count rows matching either of two values?
COUNTIFS alone can't do OR logic. Add two COUNTIFS together, one per value, or drop both values into an array constant like COUNTIFS(A:A,{"Yes";"Maybe"}) wrapped in SUM.
Why does COUNTIFS keep giving me a #VALUE! error?
Almost always because the criteria ranges aren't the same size - same number of rows and columns across every pair. COUNTIFS is strict about that.
Need to translate a formula using COUNTIFS?
Use our translator to convert your complete formula