Sommaire

## Usefulness of the COUNTIFS function

**The COUNTIFS function allows you to count the number of rows corresponding to criteria.**

In fact, it's a bit like filtering your data and counting the rows that match your selection criteria. Except here, it's the function that returns this number for you 👍😍😎

COUNTIFS allows you to count between 1 and 255 criteria. The COUNTIF function can only do this on one criterion.

## Repro the formula here

Throughout this article, you will be able to try the different formulas in this workbook. It's not an image, it's a workbook 👍🏆

## The formula COUNTIFS

The function COUNTIFS is very easy to build 😎

- Selecting a range of cells where is your criterion (the value you look for)
- The selection criterion for this data range
- and so on for the other 255 criteria...

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

If you select more than one range, **you must select** the same number of rows.

## Example with 2 criteria

If you want to know how many single men are in your dataset, follow these steps

- 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 criterion

- Select the range
**E2:E20** - Write the criterion "
**Single**"

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

Note: It is

compulsorythat the range of your cell are exactly the same dimension. ⚠⚠⚠

For example COUNTIFS= C2:C**20,**"Man",E2:E**21,**"Single") will return the * #/VALUE!* because the ranges are not equal.

## Replace the criteria by reference

Instead of writing your 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)

## Related articles

Have a look at these other articles that could help you in your work

## 1 comment

## PARVATHY .V

19/01/2018 at 05:50 (UTC 1) Link to this comment

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