COUNTIFS – COUNTIF FOR MULTIPLE CRITERIA
In my last post I talked about SUMIFS – SUMIF FOR MULTIPLE CRITERIA, this time I’m going to talk about COUNTIFS, which you may have guessed is a way to carry out a COUNTIF for multiple criteria.
Before continuing, you should be comfortable with the COUNTIF function, click here to read my post on COUNTIF.
Note the plural and singular COUNTIFS & COUNTIF – the difference between them is important!
COUNTIFS SAMPLE DATA
Our COUNTIFS post will be based on the table relating to sales data below.
With our COUNTIF formula we can count how many times Alan made a sale, how many sales there were in the East, how many sales were for more than 8,000 etc.
In each case, we can count one particular type of criteria.
What if we wanted to know how many sales Alan made in the East?
This is where COUNTIFS comes into it.
COUNTIFS
COUNTIFS “Counts the number of cells specified by a given set of conditions or criteria”.
The syntax for the COUNTIFS formula is =COUNTIFS(criteria_range1, criteria1,…).
You can add more criteria to this.
For our first one, we will count how many sales Alan has made in the West.
Let’s start in cell E2
- criteria_range1 will be column A – the cells that we want to count based on our first criteria
- criteria1 is “Alan” – our first criteria will be the name “Alan”
- criteria_range2 will be column B – the cells that we want to count based on our second criteria
- criteria2 will be “West” – we want to count how many sales Alan (criteria1) has made in the West (criteria2)
Our full formula is =COUNTIFS(A:A,”Alan”,B:B,”West”).
We could add more criteria to this if we wanted, for example sales greater than 5,000, just by adding the 3rd criteria range and 3rd criteria.
That is the COUNTIFS function in a nutshell.
If you understand and have used the singular COUNTIF function, then the COUNTIFS function shouldn’t be a problem for you.
As always with Excel, there are usually a few ways to get the same information and this is just one of them, some people prefer PivotTables or other methods, this is just one of them.
