Excel Formulas : Countif Multiple Criteria

Many times we need to perform a count based on multiple criteria.

This is very simple if you are using Excel 2007. You have standard excel function Countifs which lets you do the count based on multiple criteria.


But if you are using Excel 2003 this becomes tricky as you do not have any standard function that lets you do this. However you can do it using Pivot Tables. Sometimes you just need the count based on multiple criteria to be put into a report and using Pivot tables for such report may not be a viable option.

In such scenarios you need to go for a workaround using excel formula. Considering you have a data table like this and you want to know the number of customers in North Area serviced by Sales Man by name of Ram.

If you have Excel 2007 then it very simple just use
=COUNTIFS($B$13:$B$28,"North",$C$13:$C$28,"Ram") and it will give you count result 3.

For Excel 2003 users My favorite for such kind of calculation is Sumproduct function and you can do it with the help of following formula.
=SUMPRODUCT(($B$13:$B$28="North")*($C$13:$C$28="Ram"))
This formula will give you result as 3 customers.
You can add as many conditions here but be sure that the height of the range is same for all ranges mentioned in this formula

This actually is a workaround for another method array formula.
=SUM(($B$13:$B$28="North")*($C$13:$C$28="Ram")) confirmed with CTRL+Shift+Enter
Once you confirm this formula with CTRL+Shift+Enter it will add {} to the formula which will be visible in the formula bar only.

Look at the screen cast below to know the difference it make to the normal formula once confirmed as CSE formula. Look for the addtional {} added to formula

Download file having countif multiple conditions formula

You may find it difficult to enter array formula that is why I suggest you to go for SUMPRODUCT method.
To know more about array formulas you can read

Introducing array formulas in Excel

.