Using Excel’s COUNTIFS Function

A new function for Excel 2007, the COUNTIFS function returns the number of cells (of a specified range), that match a set of given criteria. So let’s say in the example below you want to know how many males are over the age of 25. With the new COUNTIFS function you can count using multiple ranges and criteria – in fact up to 127 pairs.

Using the standard COUNTIF function you could only specify one range and one criteria.

How to use Excel’s COUNTIFS Function to Count Males over 25


using-excel-countifs-functionTo get started let’s look at how the format of the function should be structured.
COUNTIFS( Criteria_Range1, Criteria1, [Criteria_Range2], [Criteria2], ... ) .

Right, so in my example mentioned earlier we are going to count how many males there are that are over 25 years of age. In the picture you can see gender and age in column A and B respectively. Select a cell to write the function in. Remembering the format of the function above type in a cell the following:

=COUNTIFS(A1:A10,"Male",B1:B10,">25")

using-excel-countifs-function-1Because Male is a text string and 25 is an expression these both have to be within “” marks. If the function was count how many males are 25 then only the male would be within “” marks. If that were the case then the function would be written like so:

=COUNTIFS(A1:A10,"Male",B1:B10,25)

Remember that each criteria range must have a criteria specified. They work in pairs.

I hope you found this post useful. Please do leave comments about the article and the website. Why not even submit a question using the contact page?

Leave a Reply

Your email address will not be published. Required fields are marked *