How to use COUNTIFS in Excel

COUNTIFS counts rows where corresponding cells meet all the criteria you specify. Use it to answer questions such as how many records belong to a name and department.

COUNTIFS syntax

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

• criteria_range: the cells Excel checks for a condition.
• criteria: the value or condition a row must meet.

Count rows matching two conditions

Suppose names are in A2:A100 and departments are in B2:B100. G2 contains a name and H2 contains a department. Count rows where both match with:

=COUNTIFS(A2:A100, G2, B2:B100, H2)

Each additional criteria range and criteria pair adds another condition. The criteria ranges should have the same dimensions.

COUNTIFS or SUMIFS?

COUNTIFS returns the number of matching rows. Use SUMIFS when you need to add numeric values from those rows instead.

Continue to the Excel lookup learning path or browse all Excel tutorials.