COUNTIFS – Count rows automatically in Excel

COUNTIFS – Count rows automatically in Excel
Last Updated on 30/03/2024
Reading time: 2 minutes

The Excel function COUNTIFS returns the number of rows found in your document for a specific lookup

  1. The column containing the first criterion

    Select the column where is the first criterion

  2. First criterion

    Write the value of the first criterion

  3. [optional] The column containing the second criterion

    Select the column where is the second criterion

  4. [optional] Second criterion

    Write the value of the second criterion

=COUNTIFS(Column with the first criteria, First criteria, Column with the second criteria, Second criteria, ....)

Don't count your rows with the filter ⛔⛔⛔

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

Filter header

Why not for one or two research? But for more, it's a waste of time and you must use another technique.

COUNTIF vs COUNTIFS in Excel

There is no difference between these 2 functions in terms of performance. The only difference is the number of criteria you can fill.

  • With COUNTIF you can count the rows for 1 criterion

=COUNTIF(Range1,Criteria1)

  • With COUNTIFS you can count the rows for 255 criteria

=COUNTIFS(Range1,Criteria1,Range2,Criteria2,Range3,...)

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

How to count rows with 2 criteria?

For example, how many single men are in your list?

Example of data in your workbook
  1. Start by writing the formula COUNTIFS
  2. Select the range of cells where you want to search your criterion: column C
  3. Enter the value or the text, you want to search; here "Man".
  4. Select the range E2:E20
  5. Write the value "Single"

=COUNTIFS(C2:C20,"Man",E2:E20,"Single")

COUNTIFS with 2 criteria

Watch your range of cells!!!

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

=COUNTIFS(C2:C20,"Man",E2:E21,"Single")

Error when the size of the range is different

Replace the criteria with a 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 😎😀

=COUNTIFS(C2:C20,H1,E2:E20,"Single")

Use the cells reference as criterion with COUNTIFS

Greater than / Lower than

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

COUNTIFS Greater than with reference

8 Comments

  1. SAUL
    27/06/2019 @ 22:06

    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

    Reply

  2. PhilW
    07/06/2019 @ 20:52

    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?

    Reply

    • Frédéric LE GUEN
      09/06/2019 @ 15:09

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

      Reply

  3. kazi
    26/12/2018 @ 11:58

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

    Reply

    • Frédéric LE GUEN
      08/01/2019 @ 09:13

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

      Reply

  4. PARVATHY .V
    19/01/2018 @ 05:50

    THIS SIGHT WAS VERY USFULL LIKE ME NOT MORE EDUCATED THANK YOU

    Reply

Leave a Reply

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

COUNTIFS – Count rows automatically in Excel

Reading time: 2 minutes
Last Updated on 30/03/2024

The Excel function COUNTIFS returns the number of rows found in your document for a specific lookup

  1. The column containing the first criterion

    Select the column where is the first criterion

  2. First criterion

    Write the value of the first criterion

  3. [optional] The column containing the second criterion

    Select the column where is the second criterion

  4. [optional] Second criterion

    Write the value of the second criterion

=COUNTIFS(Column with the first criteria, First criteria, Column with the second criteria, Second criteria, ....)

Don't count your rows with the filter ⛔⛔⛔

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

Filter header

Why not for one or two research? But for more, it's a waste of time and you must use another technique.

COUNTIF vs COUNTIFS in Excel

There is no difference between these 2 functions in terms of performance. The only difference is the number of criteria you can fill.

  • With COUNTIF you can count the rows for 1 criterion

=COUNTIF(Range1,Criteria1)

  • With COUNTIFS you can count the rows for 255 criteria

=COUNTIFS(Range1,Criteria1,Range2,Criteria2,Range3,...)

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

How to count rows with 2 criteria?

For example, how many single men are in your list?

Example of data in your workbook
  1. Start by writing the formula COUNTIFS
  2. Select the range of cells where you want to search your criterion: column C
  3. Enter the value or the text, you want to search; here "Man".
  4. Select the range E2:E20
  5. Write the value "Single"

=COUNTIFS(C2:C20,"Man",E2:E20,"Single")

COUNTIFS with 2 criteria

Watch your range of cells!!!

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

=COUNTIFS(C2:C20,"Man",E2:E21,"Single")

Error when the size of the range is different

Replace the criteria with a 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 😎😀

=COUNTIFS(C2:C20,H1,E2:E20,"Single")

Use the cells reference as criterion with COUNTIFS

Greater than / Lower than

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

COUNTIFS Greater than with reference

8 Comments

  1. SAUL
    27/06/2019 @ 22:06

    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

    Reply

  2. PhilW
    07/06/2019 @ 20:52

    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?

    Reply

    • Frédéric LE GUEN
      09/06/2019 @ 15:09

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

      Reply

  3. kazi
    26/12/2018 @ 11:58

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

    Reply

    • Frédéric LE GUEN
      08/01/2019 @ 09:13

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

      Reply

  4. PARVATHY .V
    19/01/2018 @ 05:50

    THIS SIGHT WAS VERY USFULL LIKE ME NOT MORE EDUCATED THANK YOU

    Reply

Leave a Reply

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