How to Use COUNTIFS in Excel: Syntax and Examples

Use the COUNTIFS function in Excel to count the number of cells that meet multiple criteria across one or more ranges. The syntax is COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...), where each criteria_range is the range to check and criteria is the condition to meet.

Syntax

The COUNTIFS function counts cells that meet all given conditions. It requires pairs of criteria ranges and criteria.

• criteria_range1: The first range to check.
• criteria1: The condition for the first range.
• criteria_range2, criteria2, ...: Optional additional ranges and conditions.

All criteria must be true for a cell to be counted.

excel

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

Example

This example counts how many sales are greater than 100 and belong to the "East" region.

excel

A1:A5 (Sales): 120, 90, 150, 80, 200
B1:B5 (Region): East, West, East, West, East

Formula in C1:
=COUNTIFS(A1:A5, ">100", B1:B5, "East")

Output

3

Common Pitfalls

Common mistakes include:

• Using mismatched ranges of different sizes, which causes errors.
• Forgetting to put criteria in quotes when needed, like ">100".
• Using COUNTIF when multiple criteria are needed.

Always ensure ranges are the same size and criteria are correctly formatted.

excel

Wrong: =COUNTIFS(A1:A5, >100, B1:B5, East)  (missing quotes)
Right: =COUNTIFS(A1:A5, ">100", B1:B5, "East")

Quick Reference

Part	Description	Example
criteria_range1	First range to check	A1:A10
criteria1	Condition for first range	">50"
criteria_range2	Second range to check (optional)	B1:B10
criteria2	Condition for second range (optional)	"East"

Key Takeaways

COUNTIFS counts cells that meet all specified conditions across ranges.

Always use quotes around criteria with operators or text.

Ensure all criteria ranges are the same size to avoid errors.

Use COUNTIFS instead of COUNTIF when you have multiple conditions.