**COUNTIFS is one of the most useful functions in Excel.**

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

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

## The COUNTIFS function in Excel

**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 and 255 criteria.

## Arguments for COUNTIFS

The function COUNTIFS is very easy to use

- Select the first range of cells where is want to find your criteria
- Write your criteria
- Repro the steps for other criteria

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

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

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

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/

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?

09/06/2019 @ 15:09

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

26/12/2018 @ 11:58

Hi, the explanation is very nice and is very simple.

However, is it possible to provide the exercise sheet?

08/01/2019 @ 09:13

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

19/01/2018 @ 05:50

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