Table of Contents

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

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 😎

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

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

## COUNTIF vs COUNTIFS

The difference is just the number of criteria

- With
**COUNTIF**you can count the rows for**one criterion**

=COUNTIF(Range1,Criteria1)

- With
**COUNTIFS**you can count the rows for**256 criteria**

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

The construction is the same, you just add more criteria

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

=COUNTIFS(C2:C**20,**"Man",E2:E**21,**"Single")

## Replace the criteria with reference

Instead of writing the value of the criteria directly in the formula, you can use the contain of a cell by using the reference of the cell.

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

## 5 comments

Skip to comment form

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?

Author

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

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

However, is it possible to provide the exercise sheet?

Author

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

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