How to use the COUNTIFS function

Updated on: March 16, 2023

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!


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


  • 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.

etwUQ AtuATkCE9MfQ6beMN MAWEXp7WYuIgn8v7NKYCU51n4inVXep7

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)

qQPq xKJBol4DN7P3grG k KmRNgH0cuUiKreeG1ZgiTXCwKjjOdzV67tPKjDqjvFooWx0Bvpz5bnojsXhI94y jOIQ9jZIEjTQGii0 2qfb16k8P8gXI8QrJ8wM9ZzAKQ FIcTEeqlrawPInbAMDDU

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:


ozmtkaslfZlVoAGVwF9pJdYKx4OqXSOE5BSMcac5x3K6FxOv7 WS0uhBxiorqDURT7Ml6AKPLbUmyn6tNur2yqRBQfXBTv9fDX7t

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:


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

dlHQpnxgQS0uxpVFgMSVnwsiW B2VLuNnPyDAEfbW7dY7gIjSbxSGHx9PlnOpnQjERdsxT fwNLUSfJg4r3V4OvmxLPUd

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:


NhS OH5wTSg1rgwNoJumtqRgS7wrjEgBhVRAKCWQ010yr4

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:

T7CPOxeDtewdKJ8jRGm82dPPeHuLQM DvPnpYbzcFS anYXi5jLVgr8yVdsVG5Mc1iH

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:


ytZaZpeJ7m8vKs3FtcMMTQRdkyze0V9n60TJufpL9e16JDuy6ze Z5hqGQOGtZHdOZILIQ7nRlHq hls 3mIY2CgCbYkQLsyL9qAEZH0ps2 gk73fpAX8EePUYFVcQaMWo6J79fIjotcDI 4w9pTk5s

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! 

Lorena M. Rodas leverages her experience across film, writing, and production to make complex tech concepts accessible through storytelling. With a background spanning sci-fi, AI, and emerging tech, Lorena translates her depth of knowledge into engaging, educational content. She is an expert at decoding high-level topics to reach broad audiences.
Access 4,242+ video lessons. Instantly.
Start Learning Now →
crossmenu linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram