What does it do?
Counts the number of cells that matches your specified conditions
Formula breakdown:
=COUNTIFS(range1, criteria1, [range2], [criteria2], …)
What it means:
=COUNTIFS(range of cells to check1, condition to check against1, [range of cells to check2], [condition to check against2], …)
Do you have a scenario where you want to count the number of cells that match specific conditions?
I’m sure you do! There is a simple way to count this with Excel’s COUNTIFS formula!
This is very similar to the CountIf Formula! The only difference is it allows you to add even more conditions as needed…That’s POWEFUL!
The COUNTIFS formula is very flexible indeed, so let us try to count the following from our Excel worksheet:
- Number of times John got more than 10,000 sales
- Number of times Kim got more than 18,000 sales
I explain how you can do this below:
STEP 1: Let us target the first question: How many times John got more than 10,000 sales?
We need to enter the COUNTIFS function in a blank cell:
=COUNTIFS(
STEP 2: The COUNTIFS arguments:
Table of Contents
range1, criteria1
What is our first condition?
We want to find the names that match “John”
=COUNTIFS(A9:A13, “John”,
range2, criteria2
What is our second condition?
We want to find sales that are more than 10,000
=COUNTIFS(A9:A13, “John”, C9:C13, “>10000”)
You now have your count of 2!
STEP 3: Now let us try doing the same for Kim!
range1, criteria1
What is our first condition?
We want to find the names that match “Kim”
=COUNTIFS(A9:A13, “Kim”,
range2, criteria2
What is our second condition?
We want to find the sales that are more than 18,000
=COUNTIFS(A9:A13, “Kim”, C9:C13, “>18000”)
You now have your count of 1!
You can have more than 2 conditions in the COUNTIFS formula, so go crazy with the COUNTIFS!
CountIfs Formula in Excel
