How to use the COUNTIFS formula with multiple criterias.
In this next example we will build a simple, but posh little tool that you can wow your managers with. This is why you are here right?
It will be based on the COUNTIFS formula, which is the big brother of the COUNTIF described here.
What can I do with the COUNTIFS formula and when would I use it.
The COUNTIFS formula can count occurrences in any set of data, based on multiple criteria.
Let’s say you get a large set of data of the sales division of your company, and they ask you to extract, the number of sales by sales person, but also by regions.
In this lesson we will:
Output one line
On that line we will make two drop down lists
(One with the salesperson’s name, and one with the region)
Write a COUNTIFS formula looking for those (now selectable) criterias
Making the drop down lists with Data Validation
Have you ever seen cells in excel workbooks that you can click in and it turns to a drop down menu, with pre-defined list? They are very simple lists called drop downs,
You can set them up in the Data Validation section in Excel.
All it needs is that you click in the cell, and set up the data validation so you can only enter a value to the cell from a pre-set list. This is important because we limiting a user with a set of validation rule to only enter a simple type of value. Therefore we can be sure that our formulas will work.
Adding the COUNTIFS formula with two criterias
The COUNTIFS formula is almost the same as the little brother COUNTIF.
It asks us to input the following parameters:
criteria_range1 (This is where the range is for the first given criteria)
criteria1 (This is the first criteria for the range above)
criteria_range2 (Range for the second criteria)
criteria2 (Second criteria)
and so on so forth….
We can describe up to 127 criterias for our counting operation.
Finally what we going to do in the next example is to write the excel countifs formula so the first criteria is asking to find the name in our drop down list (on cell G3), and the second criteria will be the region in our drop down list (on cell H3).
This way our user can select a name, and a region and get the number of sales for that person in the selected region.
Note: We locked the range and the criteria both ways within the countifs formula as we don’t want anything to move.
We have created a facebook page of ExcelAngel check us out!