# COUNTIFS – Count rows automatically in Excel

Last Updated on 04/05/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.

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?

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")

## 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")

## 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")

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

## Frédéric LE GUEN

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

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?

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

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?

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

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

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

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

# COUNTIFS – Count rows automatically in Excel

Last Updated on 04/05/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.

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?

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")

## 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")

## 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")

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

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

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?

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

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?

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

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

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

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