↑ Return to Condition

COUNTIFS Function

The COUNTIFS function complete the lake of the COUNTIF function. It is only available for the version of Office 2007. For earlier versions, unfortunately there is no equivalence 🙁

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

=COUNTIFS(

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

=COUNTIFS(C2:C20,

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.

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

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:

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

Note: It is compulsory that the range of your cell are exactly the same for the ” criteria. For example COUNTIFS= C2:C20,“Man”,E2:E21,“Single”) will return the #/VALUE! Because the two ranges are not equal.

 

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 Total. The formula is:

=COUNTIFS(B2:B5000,”Smith”,H2:H5000,”>1500″)

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 operator >= and the cell reference. Without this symbol, your test can’t work

=COUNTIFS(B2:B5000,J3,H2:H5000,”>=”&,J4)

 

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.

=COUNTIFS(B2:B5000,B3,

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:

=COUNTIFS(B2:B5000,B3,A2:A5000,”>=01/12/2008″,A2:A5000,″<=31/12/2008″)

or

=COUNTIFS(B2:B5000,B3,A2:A5000,”>=”&K2,A2:A5000,″<=″&K3)

Count over a variable time period

Obviously, best is to change the date without changing your formula every month.

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:

=COUNTIFS($E$2:$E$5000,$K$2,$A$2:$A$5000,”>=”&DATE($K$2;$K$3;1),
$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

  1. Omar Bin Zaid

    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

  2. munendrarathore01gmailcom

    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

  3. Vitalijs

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

    Excel does not allow save it. What is wrong?

  4. Phano

    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.

  5. Anonymous

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

  6. Medo

    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.

  7. juana de arco

    im gonna stop doing this shit

  8. juana de arco

    i dont undersatand

  9. juana de arco

    dale like

  10. Andy Chin

    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?

  11. satheesh

    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

  12. pavlos

    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?

  13. Mike

    =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!

  14. ripudaman singh

    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.

    1. Frédéric LE GUEN

      Can you explain more please ?

  15. ADEY

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

    1. Frédéric LE GUEN

      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

      1. Anonymous

        =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

  16. beaucoupjack

    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”

  17. Ray

    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?

  18. Frédéric LE GUEN

    Hi Bob,
    No, you must right 2 times your column G as Range
    =Countifs(E2:E100, G2:G100, “>=25”, G2:G100, “<27")

  19. Bob

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

Leave a Reply

%d bloggers like this: