Explanation of the COUNTIFS function
The COUNTIFS function allows you to evaluate for multiple criteria with the following argument structure:
- criteria_range1, criteria1
- criteria_range2, criteria2
- Note: You can input up to a max of 127 [criteria_range, criteria]
In the image below I purposely typed a few commas to force the argument description to show multiple criteria.
- COUNTIF –> evaluates for only 1 criteria
- COUNTIFS –> evaluates for multiple criteria. Note: You can still use the COUNTIFS function if you only have 1 criteria. In this way it will act like a COUNTIF function.
Example data used to explain how the COUNTIFS function works
- 3 different colors
- each color has 3 different sizes
- 2 locations (color coded to easily distinguish them apart)
- Quantity sold at each of the 2 locations
1. criteria_range1, criteria1 (1st and 2nd arguments)
The COUNTIFS function will use the criteria_ranges to find a match against your criteria for each pair of [criteria_range, criteria] that you provide.
Note: You can input up to 127 pairs of [criteria_range,criteria].
How does the COUNTIFS function work?
You should use the COUNTIFS function if you need the count of when multiple criteria are true.
The function will increase the total count by 1 for every row where it finds a match of each criteria in the corresponding criteria_ranges (which is evaluated for each pair of [criteria_range,criteria]).
For each row within your data set:
- If at least 1 of the criteria does not match the value from its criteria_range –> the row is ignored from the total count
- If each criteria matches the value from its criteria_range –> the count is incremented by 1
Keep in mind the COUNTIFS function syntax when reading the Formulas shown below… =COUNTIFS(criteria_range1,criteria1,….<all the way to>….,criteria_range127,criteria127)
Go back to Excel Tutorials.