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

**The column containing the first criterion**Select the column where is the first criterion

**First criterion**Write the value of the first criterion

**[optional] The column containing the second criterion**Select the column where is the second criterion

**[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 😉

## Example to count rows with 2 criteria

How many **single** **men** are in your data set?

- Start by writing the formula COUNTIFS

=COUNTIFS(

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

=COUNTIFS(C2:C20,

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

=COUNTIFS(C2:C20,"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(C2:C20,"Man",E2:E20,"Single")

## Watch your data ranges !!!

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

=COUNTIFS(C2:C**20,**"Man",E2:E**21**,"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.

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

Frédéric LE GUEN

28/06/2019 @ 13:40

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

https://www.excel-exercise.com/dynamic-sum-in-excel/

sivaraman

24/08/2023 @ 05:25

very good

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

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

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

PARVATHY .V

19/01/2018 @ 05:50

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