COUNTIFS – Count rows automatically in Excel

COUNTIFS is one of the most useful function in Excel. The function returns the number of time a word or a value has been found in a list of data.

Avoid the filter to count rows

When you want to count the number of rows corresponding to a specific criteria, many users use the tool Filter.

Filter header

For 1, 2 or 3 different criteria, you can work like that. But if you have to extract a lot of count of rows for many different criteria, the filter is not the good tool.

Use the function COUNTIFS

The COUNTIFS function counts the number of rows corresponding to one or many criteria.

The COUNTIFS has been introduced since Excel 2007 and allows you to add between 1 or 255 criteria.

You can also use the COUNTIF function  but this function only use 1 criteria.

Argument for COUNTIFS

The function COUNTIFS is very easy to build 😎

  1. Select the first range of cells where is want to filter
  2. Write the value of your criteria
  3. and so on for the other criteria...



The difference is just the number of criteria

  • With COUNTIF you can count the rows for one criterion


  • With COUNTIFS you can count the rows for 256 criteria


The construction is the same, you just add more criteria 😉

Example with 2 criteria

How many single men are in your data-set?

Example of data in your workbook
  • Start by writing the formula COUNTIFS


  • Select the range of cells where you want to search your criterion (the column C)


  • Enter the value,or the text, you want to search (here "Man").


The word "Man" is written between double-quotes because it's a string and all strings must be written between double-quotes in Excel.

Then, for the second criteria

  • Select the range E2:E20
  • Write the value "Single"


COUNTIFS with 2 criteria

Watch your data ranges ❗❗❗

If the function COUNTIFS returns #VALUES!, is because you haven't selected the same range of cells for each criteria.


Replace the criteria with reference

Instead of writing the value of the criteria directly in the formula, you can use the content of a cell by using the reference of the cell 😎😀


The criteria of the COUNTIFS function is the reference of a cell

Greater than / Lower than

You can create more complex criteria with the logical symbol liker greater than (>) or lower than (<). Have a look at this article to build your COUNTIFS function in this situation.

COUNTIFS Greater than with reference

Related posts

Permanent link to this article:


Skip to comment form

    • SAUL on 27/06/2019 at 22:06
    • Reply

    if I Have created a formula for ranges let's say A1:B40, in one work sheet that extract data from a second worksheet,
    when I received the new updated work sheet, new rows have been added, and my formulas will not cover them automatically
    to be updated. is there a code to verify that no new rows have been added and if new rows where added the formula
    automatically gets updated and instead of reading A1:B40 would increase the formula to cover the new rows A1:B120

    1. If you put your data inside a Table, the references of your data will be automatically updated

    • PhilW on 07/06/2019 at 20:52
    • Reply

    I have an array on one sheet (B2:F2002). I want to count how many instances of one number in any row is followed by a different number in the following row (i.e. how many times is a number 1 in a row of the array followed by a number 2 in the next row of the array). Is there a way to do this?

    1. Not sure to understand your problem. Post your message on Probably someone will answer your question

    • kazi on 26/12/2018 at 11:58
    • Reply

    Hi, the explanation is very nice and is very simple.
    However, is it possible to provide the exercise sheet?

    1. No, I don't give my exercises. That's my job too

    • PARVATHY .V on 19/01/2018 at 05:50
    • Reply


Leave a Reply

Your email address will not be published.