How to use the COUNTIFS function

You may have heard about the COUNTIF function as an excellent aid in counting and summarizing the cells with a specific criterion. Or, on the other hand, you might have used the IF functions instead. However, an advanced tool allows you to combine the prior two and help you find several conditions at a time. Well then, are you ready to know how to use the COUNTIFS function? Let’s see:

• Syntaxis:
• Insert & Example:
• The numeric criteria
• Cell reference Criteria
• Logical Criteria

Note: The COUNTIFS function was introduced in Excel 2007, so it's not available in earlier versions of Excel. If you're using an earlier version, you can use the COUNTIF function multiple times to achieve the same result.

So, are you ready to start? Then, open your worksheet and keep reading to discover how practical this function can be!

Syntaxis:

You can access this function by writing and understanding the following concepts:

=COUNTIFS(Critera_range1;criteria1;[Critera_range2;criteria2]...

• Critera_range1: This is the first range Excel will consider to count the criterion.
• Criteria1: This is the first criterion directly linked to the first range.
• Critera_range2 & criteria2: The following ranges and criteria you can add.

Note: You must not necessarily put the following ranges and criteria between brackets.

Insert & Example:

Thus, to start using this function, click on one of the blank cells where you need to put the information and type the syntaxis following the same conditions as if using the COUNTIF function. Check this example:

=COUNTIFS(A1:A34; “Jane Doe”;B1:B34;“Winner”)

However, if you have a table, you must write the table’s name so that Excel will consider the range based on the header’s name.

So, after you enter into that formula the conditions you want to search for, Excel will find the cells that satisfy them and give you a result.

But on the other hand, if you need to look for cells that accomplish at least one of specific criteria, you must change the base syntaxis a little and use the COUNTIF function as well:

=COUNTIF(Range; Criteria)+COUNTIF(Range;Criteria)

The numeric criteria

As you can see, the COUNTIF and COUNTIFS functions have many things in common, such as some criteria. Nonetheless, when using the multi-conditionals operation, remember that you must enter the text condition between quote marks in contrast to the numeric criterion:

=COUNTIFS(Range;“TextCriteria”;Range2;Number).

Cell reference Criteria

Of course, you can also use other cells to search among your table for the value you need. This can be especially handy when creating reports for your client or boss, making the process more interactive. 

To start, you only must type in a blank cell the criterion you need following this template:

=COUNTIFS(Range1;CellReference1;Range2;CellReference2…)

Example: =COUNTIFS(A1:A11;G1;B1:B11;G4)

Logical Criteria

When talking about logical criteria, you must immediately think about the following operators to specify the conditions:

• > is greater than.
• < is less than.
• = is equal to.
• >= is greater or equal to.
• <= is less than or equal to.
• <> is not equal to.

For example, to count the number of cells in a range equal to "Vegetables," you would use the criteria "Vegetables." 

The syntaxis you must write is:

=COUNTIFS(Range1;”LogicalOperator+Value”...)

Hence, as you can see, you must type the condition between quote marks followed by the logical operator and the item, whether it is a text criterion or a numeric value. Check this other example:

However, when you need to use logical criteria with a cell reference, you must add the ampersand sign followed by the cell you need without quotes:

=COUNTIFS(Range1;”LogicalOperator”&CellReference).

Finally, just like the COUNTIF function, you can also use some wildcards if you need to, such as the asterisk, which will help you find and count data similar to something you are looking up.

Do you want to know more?

You may want to know more about Excel functions, like the IF or COUNTA operations. Or perhaps, you are more interested in finding errors in Excel! Everything you need to know about Excel in our Excel course here at Skillademia! Good luck!