COUNTIFS

COUNTIFS counts the rows that meet all of several conditions: =COUNTIFS(criteria_range1, criteria1, …). It answers questions like how many orders from the East were over $500, or how many tickets are open and high priority.

When to use it

• You need a count filtered by two or more columns.
• You count items within a range of dates or numbers.
• You build dashboards that count by status and owner.
• You want AND logic without helper columns.

Arguments explained

• criteria_range1. The first range to test. Every criteria range must have the same number of rows and columns.
• criteria1. The condition applied to criteria_range1, written like COUNTIF criteria: a value, ">=10", "<>" or a cell reference.
• criteria_range2, criteria2, …. Optional extra pairs, up to 127. A row is counted only when every pair is satisfied, which is AND logic. For OR logic add two COUNTIFS together.
• Date ranges. To count between two dates, use the same range twice: COUNTIFS(A:A,">="&D1,A:A,"<="&D2).

Worked examples

Common errors and how to fix them

• #VALUE!. The criteria ranges have different sizes. They must all match.
• Count is 0. Criteria with trailing spaces or mismatched number and text formats do not match.
• OR logic. COUNTIFS combines criteria with AND. Add separate COUNTIFS for OR.
• Dates not counted. Build date criteria with ">="&A1 using real dates, not text such as "1/1".

Related functions

• COUNTIF
• SUMIFS
• AVERAGEIFS
• FILTER
• SUMPRODUCT

Frequently asked questions

What is the difference between COUNTIF and COUNTIFS?

COUNTIF takes one condition; COUNTIFS takes up to 127 range and condition pairs.

How do I count between two dates?

Use two criteria on the date column: ">="&start and "<="&end.

Can I use wildcards?

Yes, in text criteria: "A*" and "?n*".

How do I count blanks with COUNTIFS?

Use "" as the criteria for an empty cell.