COUNT and COUNTIF function are very popular and useful for the people who deal with Ms-Excel. Ms-Excel has a wide range of functions. Recently I learn about COUNTIFS() function.
COUNTIFS was introduced with MS-Excel 2007 and it works similar to COUNT and COUNTIF i.e. it returns number of rows. The best thing about COUNTIFS is it gives us functionality to apply multiple filtering criteria. It mainly has two parameters.
COUNTIFS(Range, criteria)
In the range parameter we need to supply the cell range of data that we are searching for. If your entire data is in 5 rows and 5 columns then you can pass A1:E5 as range parameter. In the criteria parameter we need to supply the condition or data that we are searching for. The best thing about criteria parameter is it accepts string and logical expression(>, <, =).
Let us understand it with a basic example. We have following data in the excel sheet.
Now if we have to find out number of items Vendor Samsung supplies. We can use COUNTIFS() function. We can pass the two parameters as discussed above.
=COUNTIFS(B3:E18,"=Samsung")
This will return a row count of 3. So Samsung is the vendor for three products.
Now, let us find out how many products Samsung supply which costs above $200
=COUNTIFS($C$2:$C$18,"=Samsung",$D$2:$D$18,">200")
This will return a row count of 2. So Samsung is the vendor for two products which costs more than $200.
Now, let us find out how many products Samsung supply which costs above $200 and quantity is more than 10.
=COUNTIFS($C$2:$C$18,"=Samsung",$D$2:$D$18,">200",$E$2:$E$18,">50")
This will return a row count of 2. So Samsung is the vendor for two products which costs more than $200 and has quantity more than 10.
So as you can see COUNTIFS() function can accepts multiple criteria. It supports up to 27 range and criteria.
The important thing to consider about COUNTIFS function is the range should be same in all multiple criteria that we apply.
