# Function COUNTIFS

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 compulsory that the range of your cell are exactly the same dimension. ⚠⚠⚠

For example COUNTIFS= C2:C20,"Man",E2:E21,"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 (Champney, Byerly, Gaudefroy, Fordyce, Freyer)