Contents

## Presentation

The COUNTIF function allows you to count how many times you have an item in a list. But when you want to do a count on several criteria, the function COUNTIF shows its limits.

That’s why Microsoft’s development teams have built since Office 2007 a new feature of counting multiple criteria, it’s the **COUNTIFS** function. The maximum number of criteria that can be integrated in this COUNTIFS is … **256** (wow) 🙄

## Construction

To function, the function **COUNTIFS** needs one or more criteria and also the beaches of data. For example, we will have the following example where you want to know how many single men are in your customer list.

We’ll start by writing the formula

Then you specify the range of cell where you want to search your first criteria (the column C)

Next, you enter the value or the text you want to search (“Man”). You can write this term directly or the cell which contains this value.

The word “Man” is written between double-quotes because it’s a string and all strings must be written between double-quotes in Excel.

Following this first test, you will select the column E as the second range of criteria and then you write the word “Single” as the second criteria:

Note: It is compulsory that the range of your cell are exactly the same for the ” criteria. For example COUNTIFS= C2:C

20,“Man”,E2:E21,“Single”) will return theBecause the two ranges are not equal.#/VALUE!

## Greater than / Less than

In the same way as the function COUNTIF, you can add logical symbols **“>”, “<“, “>=”, “<>”** to expand your criteria of selection.

Consider the following example you want to count the number of orders that exceed **1500** for the client Smith. In this exercise, we will make a count on the * names* and on the column

*The formula is:*

**Total.**Now, if you write your formula not with the value but with the cell references, you will write your formula as follow:

Note: the symbol

&is needed to link the operatorand the cell reference. Without this symbol, your test can’t work>=

## Count over a period of time

With the **COUNTIFS** function, it is also possible to calculate the number of elements for a specific period of time and without difficulties.

In the following example, we will try to calculate the number of times we sold hard drives between the 1rt December 2008 to the 31th December 2008.

Our first criteria is very simple. B2:B5000 to the reference range and J3 (** Hard Drive**) for the criteria.

Then we will build two criteria for the same column A (the date)

- once to get the orders greater than or equal to 01/12/2008
- another for orders under stringent 01/01/2009 (or less than or equal to 31/12/2008)

In these two cases, the reference range is the same (A2: A5000) and only the criteria will be different. The complete formula becomes:

or

## Count over a variable time period

So we will replace in our formula the dates by another formula wich returns a variable date. In this case, we will use the DATE function to find the first and last day in function of the value in K2 and K3.

So for the first date we have **DATE($K$2,$K3$,1)** and for the last date **DATE($K$2,$K3$+1,0)**. Applied to our formula:

$A$2:$A$5000,″<″&DATE($K$2;$K$3+1;0))

The last example is obviously the most complete but also the more useful because many dashboards are built on the basis of monthly reports. With this formula, you can easily change values of the month of the year and see the result immediately in cell K4.

## 22 comments

Skip to comment form ↓

## Omar Bin Zaid

01/14/2016 at 4:48 am (UTC 0) Link to this comment

Hi can help me with below table

Qty Category Total

5 BODY COND Exterior = ??

4 EXTERIOR Body Cond = ??

3 EXTERIOR Interior = ??

3 BODY COND Functional = ??

2 BODY COND Engine Room = ??

2 BODY COND Under Body = ??

2 INTERIOR.

5 UNDER BODY

How do i can get the TOTAL for each CATEGORY using QTY given, example

i need EXTERIOR total by calculate how many EXTERIOR inside the CATEGORY and QTY

## munendrarathore01gmailcom

11/02/2015 at 8:57 pm (UTC 0) Link to this comment

Hi i want your help on countifs function…

Actually i have a data in column “C” . Like 1 2 3 1 2 3 1 2 3 1 2 3 1 2 3. ye c1,c2,c3,….like in the columns we have this data.

i want a countifs function…. agar “D” column me “countifs” ka formula laga raha hu to i will find ki 1 kitne bar hai 2 kitne bar hai but everytime it should be come in a number range like c1 colume me 1 hai to d1 me show kare ki d1 is 1 ,phir agli bar jab 1 ayega then show kare ki uska count 2 show kare phir jab agli bar jab bhi 1 aaye its should show count 3 for 1.

Please help me on this mujhe ye formula bahut urgent chahiye please help me for this.

Munendra

## Vitalijs

09/28/2015 at 1:19 pm (UTC 0) Link to this comment

=COUNTIFS(F4:F246;”contract signed”;YEAR(C4:C246);Year(A1))

Excel does not allow save it. What is wrong?

## Phano

08/13/2015 at 7:06 pm (UTC 0) Link to this comment

Please give me a countifs formula to use If I want to count the number of similar pairs from numerous rows of five numbers in each row.

## Anonymous

07/02/2015 at 9:50 am (UTC 0) Link to this comment

please help me to count within the date range per month with status

## Medo

03/10/2015 at 10:33 am (UTC 0) Link to this comment

please, help me i’m looking for formula work on count number of services in column has more than one services and the second column has data of services but the problem is these 2 columns are duplicated more than once i mean i have 4 column of services and 4 for it’s date, so i want to count each services by date (month) like (count number of car services in the sep, count number of health services in oct. ) .

i hope my explain is clear.

## juana de arco

03/05/2015 at 7:04 pm (UTC 0) Link to this comment

im gonna stop doing this shit

## juana de arco

03/05/2015 at 7:03 pm (UTC 0) Link to this comment

i dont undersatand

## juana de arco

03/05/2015 at 6:56 pm (UTC 0) Link to this comment

dale like

## Andy Chin

02/04/2015 at 8:55 am (UTC 0) Link to this comment

Hi! Does anyone know how to search for the results of the “countif”-s? Like in the first example, after counting how many single men there are, is there a way to check one-by-one their names?

## satheesh

11/19/2014 at 8:15 am (UTC 0) Link to this comment

If a worksheet arranged as mentioned below, HOW CAN COUNT HOW MANY CT BECOME BETWEEN AGE OF 21 TO 25. I HAD TRIED TO CALCULATE THIS IN ANOTHER SHEET BY USING FORMULA =COUNTIFS(‘NOMINAL ROLL ‘!A:A,”=CT”,’NOMINAL ROLL ‘!C:C,”>=21″,’NOMINAL ROLL ‘!C:C,”>=25″). BUT NOT SHOWING ACTUAL RESULT. REQUEST INTIMATE A SUITABLE FORMULA TO SOLVE THE PROBLEM, PLEASE.

NOMINAL ROLL (SHEET-1)

A B C

RANK NAME AGE

CT Vinod Singh 22

CT Rajesh Kumar Purohit 24

CT VIJAY KUMAR 25

SI CHANDAN KR PASWAN 25

CT MANTU KUMAR RAM 23

CT MITHILESH KUMAR JH 24

CT PURUSHOTAM KUMAR 23

CT AJAY MANDAL 27

SI KAILASH SOREN 29

CT DEBASHIS SHIL 27

SI CHHOTU UJMODAK 30

CT DIPAK TIGGA 27

CT SUMANTA KR DAS 30

CT SAGAR DHAMALA 28

HC SANDEEP KUMAR 25

CT RAYAZ AHMAD 23

CT MONIRATH MONDAL 23

CT BABLU NAIK 23

HC PRADIP MONDAL 23

CT TAPASA ROY 25

## pavlos

10/08/2014 at 1:02 pm (UTC 0) Link to this comment

Hello.. How can I use the countifs function for multiple criteria within the same column? I have an excel sheet listing value of sale in column A and salesperson who completed the sale in column B. I want to calculate the number of sales >$100 made by Tom and Jane combined. Is this possible in a single cell, or will I have to do it in two separate cells and then add?

## Mike

08/22/2014 at 2:41 pm (UTC 0) Link to this comment

=COUNTIFS(Sheet1!C3:C32011,”=OOB”,Sheet1!D3:D32011,”=214″,Sheet1!E3:E32011,”2014,6,25″,Sheet1!E3:E32011,”2014,9,24″)

I cannot get it to count a cell that contains one date or the other. I can leave out the last criteria and it will count on the one date but I need it to count if one or the other date is in the cell. Not greater than or less than but equal to one specific date or the other specific date. I tried using the OR function but I must not be using it correctly. HELP!

## ripudaman singh

08/04/2014 at 8:48 am (UTC 0) Link to this comment

Hi team, please let me know if i need to build two criteria for the same column A (the Text).. then what operator should i use.

## Frédéric LE GUEN

08/06/2014 at 10:38 am (UTC 0) Link to this comment

Can you explain more please ?

## ADEY

06/22/2014 at 7:56 am (UTC 0) Link to this comment

i caanot understand the two numbers, and 2010 and 7… even when C&P in blank sheet it gives error… please advise.

## Frédéric LE GUEN

06/22/2014 at 9:47 am (UTC 0) Link to this comment

Hi,

2010 it’s the value for the year and 7 the value for the month. When you change the value of the year of the month, the function COUNTIFS returns the result for the month you have selected

## Anonymous

07/02/2015 at 9:54 am (UTC 0) Link to this comment

=COUNTIFS($H$18:$H$1000,”>=8/26/2014″, $H$18:$H$1000, “<=9/25/2014") how to include status for this formula example CLOSED or OPEN

## beaucoupjack

03/08/2014 at 11:04 pm (UTC 0) Link to this comment

Excel 2011 for Mac is not reading any format of cells entered into COUNTIFS as criteria. For the criteria > value in cell C1, I have tried “>&C1”, “>$C1”, “>”&C1, but nothing seems to work. Either I get the function typo error or 0 as a result. Internet examples are almost all text, i.e., “apples”

## Ray

09/13/2013 at 8:11 pm (UTC 0) Link to this comment

Love the tutorial – like to expand on it with the following:

Can one integrate the “max” function into it for example, to find the most recent sales activity or what was the biggest sale for individual?

## Frédéric LE GUEN

08/03/2013 at 10:20 am (UTC 0) Link to this comment

Hi Bob,

No, you must right 2 times your column G as Range

=Countifs(E2:E100, G2:G100, “>=25”, G2:G100, “<27")

## Bob

08/03/2013 at 2:48 am (UTC 0) Link to this comment

If I want to count the number of salesperson age 25-26, should I write =countifs(E2..E100, G2..G100, “>=25, <=26") THanks!