Sommaire

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

## Purpose of the COUNTIFS function

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

## Let's go deeper

As you can see, it's very easy to count the number of rows with the function COUNTIFS. Now let's see how we can create more sophisticate criteria.😎

### Greater than / Less than

You can enhance your criteria by adding logical operator. In other word, you can select all the rows where one criterion is greater than a value.

For example, if we want to determine the number of **men** **with incomes greater than or equal to 50,000.** The formula is

=COUNTIFS(C2:C20,"Man",D2:D20,">=50000")

If you use the reference of a cell, your formula is

=COUNTIFS(C2:C20,"Man",D2:D20,">="&H2)

⚠⚠⚠The logical operator **must be written between double quotes**.

The symbol **&** is needed to link the logical operator and the cell reference. Without this symbol, the criterion can't be understand.

## Select a range of data

If you want to return the number of men who has an income between 50,000 and 70,000, you must write your function with 3 criteria

- One for the criterion of the man
- The second for the income greater than 50,000
- The third,
**on the same range of cells**, for the value lower than 70,000

The formula is

=COUNTIFS(C2:C20,"Man",D2:D20,">=50000",D2:D20,"<=70000")

### How to do a OR with COUNTIFS

Now, if you want to count the number of rows for the women single **or** divorced. Here, you have no other choice than to create 2 formulas. One is for the single women, and the second for the divorced women.

=COUNTIFS(C2:C20,"Woman",E2:E20,"Single")+COUNTIFS(C2:C20,"Woman",E2:E20,"Divorced")

### Selection on a part of the criteria

With a computer, you can perform a research on a part of a text string.

- You can perform a research to replace one character by
**?**(T?m for Tim or Tom) - Or with a
*****for any number of characters

Most of the time, you will use *****. The ? is not really useful

If you want to find all the people with a name starting with a S, you will write the following formula

=COUNTIFS(A2:A20,"S*")

There is 2 person (Samen and Satake)

Now if you want to find all the people with a Y in the name, you will write this formula

=COUNTIFS(A2:A20,"*Y*")

And now, there is 5 people (Champne**y**, B**y**erl**y**, Gaudefro**y**, Ford**y**ce, Fre**y**er)

## 1 comment

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