Comments on: How to use Excel COUNTIFS and COUNTIF with multiple criteria

The tutorial explains how to use COUNTIFS and COUNTIF formulas with multiple criteria in Excel. You will find a number of examples for different data types – numbers, dates, text, wildcard characters, non-blank cells and more. Continue reading →

Comments page 54. Total comments: 2074

  1. Ah. no it didn't. Waste of time!!

    1. Jamie,

      Sorry for this, none of the blog engines we've tried is perfect.

      As for the formula, you should not use cell addresses inside the quotes, otherwise Excel interprets them as mere text strings. For example, instead of "<=C1" you write "<="&C1.

      Try the following formulas:

      =COUNTIFS(C2:C9, ">=6/1/2014", C2:C9, C1)

      =COUNTIFS(C2:C9, ">=6/1/2014", C2:C9, "<="&C1)

  2. That last post missed a whole chunk of something I wrote so it now probably doesn't make sense...

    I meant: From this
    =COUNTIFS(C2:C9, ">=6/1/2014", C2:C9, "=C1","=C1","<=C3")

    Hope that makes more sense and posts what I wrote.....

  3. Hi, Looking at the COUNTIFS with multiple criteria for dates....

    Is there an adjustment I can make to this formula.....

    =COUNTIFS(C2:C9, ">=6/1/2014", C2:C9, "=C1","<=C2"
    So, translation - "count everything here, that match this, between this date and that date".

    I need this to make it more user friendly than expecting people to manually adjust a formula in a cell.

    Either in the formula or with VBA?

    Thanks!

  4. Hi,

    I have a spreadsheet i am trying to count how many times a value (i.e. 3) occurs within cells E8:G380 when the text value in column D is ABC. If I use: =COUNTIFS(D8:D380, "ABC", E8:G380, "3") it returns a value error. Excel also only allows me to use two countif or countifs functions per cell.
    Any help is appreciated!

    Thank you!

    1. Hi Elodia,

      All ranges of the COUNTIFS function must be of the same size, i.e. must have the same number of rows and columns. You can use the following formula instead:

      =COUNTIFS(D8:D380, "ABC", E8:E380, "3")+ COUNTIFS(D8:D380, "ABC", F8:F380, "3")+ COUNTIFS(D8:D380, "ABC", G8:G380, "3")

  5. Dear Sir/Mam,I wanna to count objects in different cells of the same row/col., I have try so many defferent formulas,but I fail to do so,Help me.
    My Question is to add defferent cells of same row/col.
    Example:- Count objects in C1,C5,C10,C15..........,there is no range like c1:c15,so plz help me.

  6. i have 500 account number
    how to search double

  7. Col.A Col.B
    1 A
    2 B
    3 C
    4 A
    5 D
    6 C
    7 A
    8 B
    9 D

    I would like to extract from above table in a separate table like bellow the corresponding values with comma.

    Col.B Col.A
    A 1, 4, 7
    B 2, 8
    C 3, 6
    D 5, 9

    Thank you in advance!

  8. dear team

    my problem is tat a cell contains a value in general format as for eg; 2085 when i count using this countif formula the value does not get matched up and doesnt return 1 to me simply as zero

    When i entered into that cell it contains some extra characters as space if i try to delete that space and put enter the last value i.e from "2085" 5 has been truncated and im left with 208
    can u pls help me

  9. let's say i have 200 names in column with some names repeated in the list. How can i get the count other than 2 specific name?

  10. Is there not an easier formula to use to count all the cells within a column with a value that is a multiple of 5, other than:

    =COUNTIF(X4:X41,5)+COUNTIF(X4:X41,10)+COUNTIF(X4:X41,15)

    Listing every multiple of 5 is not even possible so surely there is another way??

    PLEASE help if possible......somebody....anybody? -Thanks! :)

  11. i want to count if enter one score (numbers) for one day for one employee and need to count if enter next whenever coming days that figure need to added

  12. I am trying to count two criteria. One column is counting all "New" occurances if column two is equal to a range of 15 different zip codes. I know I can countifs (a2:a20,"NEw",....) but the second criteria is d2:d20,g1:g16. I have put G1:g16 in as an "or" condition but keep getting a "0" count. Basically I want the thing to countif a2:a20 is new and d2:d20 "is one of" several different numbers. I was trying to do it without a lengthy formula referencing each number in its own countifs formula. Can you help?

  13. please advise how to use countif formula to use =COUNTIF(B6:B51, $A$54)/COUNTA(B6:B51)- every day i need to add one coloumn in B5. however it should automatically calculate this week as B5:B52, but B5 is not getting calculated.. Thankyou

    1. A B C
      1 Start Finish Days Open
      2 12/10/2014 12/13/2014 3
      3 12/15/2014 12/16/2014 2
      4 12/16/2014

      I am using =NETWORKDAYS(A2,B2) for days open
      How can i modify the above formula so it will pickup Todays's date if there is no date in B5.
      Can anybody help ASAP with that.........

  14. Hi,
    could you please advice, how to use formula if J8 contains multiple score with texts to be match with L8 (contains scoring 1,2,3). So I want if I choose/click J8 then L8 will displaying match score. Thank you

  15. Hi,
    How are you, please help me to my below problem.
    Which formula should I use from multiple condition but value will be text.
    For example: =SUMIFS(A:A,B:B,"Line",C:C,"Date")
    result factor=205 (suppose).
    But I want to the result "text", no "digit".

    So, how will I get the result "in word".....
    Please help me if you cooperated me.....
    Thanks in advance

  16. Hi i've been driven mad by excel today I hope you can help.

    I'm trying to make a critera with COUNTIF statement before my SUM value kicks in.

    As you can see the calculation works on anything less than 12 I'm trying to get cell K15 to stop counting after 12 unfortunatley K15 will have data larger than 12 so 12 would be capped if you like. is this possible?

    =COUNTIF(K15,"<13")*SUM(J15/52*4,(J15/52*K15))

    Any help would be appreciated

  17. Hi there, I'm very sure my problem will be easily fixed by one of your Excel aficionados but I'm intermediate level at best. I have complied a huge roster. I had 6 dentists and we've just taken on another 2. In the past I have manually adjusted the surgery number up to our max of 6. This has now increased to a max of 8. Can I use a countif to calculate the number of surgeries in use using the Dentists initials as criteria and increasing in increments up to 8? Rather than entering these numbers manually and adjusting if someone goes on leave etc?
    ie
    1 TT 2 SL 3 AB 4 JM 5 TL 6 JD ?CB ?PD

  18. Am I just overlooking the obvious, or is there just not an easier formula to use to count all the cells within a column with a value that is a multiple of 5, other than:

    =COUNTIF(X4:X41,5)+COUNTIF(X4:X41,10)+COUNTIF(X4:X41,15)

    Listing every multiple of 5 is not even possible so surely there is another way??

    PLEASE PLEASE help - even if the answer is simply "no". -Thanks!

  19. Hi There,

    I would be deeply grateful if anyone can help me with my issues!!!

    I am trying to set up my spreadsheet to enable me to calculate how many "1's" fall on the month of january.

    E.G

    Date Error Code(s)
    01/01/2015 1
    02/01/2015 1
    03/01/2015 1
    04/01/2015 1
    05/01/2015 1
    06/01/2015 1
    07/01/2015 1

    The idea is, I will have a break down of the error codes for each month!
    I've been trying all sorts of COUNTIF formulas but have had no luck!
    Please help, I'd be eternally thankful...

  20. Dell kiran 200
    Dell sameer 400
    Dell kiran 300
    Hp amol 200
    Zenith mohsin 500
    Dell =countif ans 3
    I want to calculate the data based on column b but calculate data form d column.I want answer if there is three dell record I want count the data
    from d column that answer Is three based on dell records.
    which functions is use for calculation
    please help me

  21. Hi,

    I am trying to make a spreadsheet that will count the number of times "A" happens between "B" occurrences. I am not really sure how to explain it so here is some info of what I am trying to accomplish.

    B
    null
    null
    null
    A
    B
    A
    A
    null
    A
    null
    A
    B

    So for the above data, I would like to count the number of times "A" appears between any given range of "B" values. So for the first two instances of "B" there would be "1" and for the range between the second and third instances of "B" there would be "4" and "null" would not be counted (they are blank cells).

    Thank you for your help.
    Luke

  22. Hi i have a problem please help me to get out this problem
    56 658 658
    65 58 258
    256 2 25
    3 88 635
    365 895 65
    987 65 658
    i want to count cell which have only two digit (example, 56,65,25 etc)

  23. I trying to have excel calculate how many times I get as close as possible to 1 with out going over, within a column of numbers in my spread sheet? Can you please help. My column is about 40 cells in length and the unit of measurement I have are random.
    Example numbers - These number will be changing more then once.
    .22
    .45
    .67
    .31
    Etc.

  24. I need the function to count the different condition in a single range. Like, In a single range numbers given 1,2,3,4,5,6,6. so, now i want to know in this range that how many numbers 1 to 3 are there.

    example: 1,1,4,4,5,6,6. answer is 2. because in this condition 1 is two times repeating from 1 to 3 series and 6 is also 2 time repeting in 4-6 series.

    Please help me.

  25. I need to use a function to count the number of Part in column c2-c134 in the NWest which is in column f2-f134, I dont seem to be able to enter the correct data into any function to get it to do this

  26. Thank you very much.. really it's very helpful

  27. I want to use Countif formula to test the criteria range against a specific logic. In other words, If column A contains a date, and I want to count the cell if its date is within a certain week number. What I did is =COUNTIFS(Violations!A:A,WEEKNUM(Violations!A:A=WEEKNUM(TODAY())))

    But it returned 0 values. Would you please check how can I do it?

  28. I have a time keeping system that is putting a "'", an apostrophe, in a a group of cells. I am trying to count the number of people that are in a range, and unfortunately the range also contains the apostrophes in otherwise blank cells. So I need to subtract the number of cells that contain the apostrophes from total number of cells. The COUNTIF does not recognize the apostrophe, I assume because it thinks it is a character to treat the cell like text rather than a date, or number, etc. (the only work around). I cannot figure out a way to fix this other than "clearing" all of the cells of apostrophes, which is very time consuming and error prone. Any ideas?

    1. Go for.. COUNTIFS (if not using using Excel2003)

      You can add new condition to exclude "apostrophes" "'".

  29. Please Help.

    I have tree sets of data. Project Type, Resource Name and Days (columns Jan-14 to Dec-14).

    Project Type being.. T1, T2, T3, T4, T5.
    Resources X, Y, Z.....
    Resource Allocation Days against each month... 17, 19, 11, 20, 20 as values

    Now I need to calculate in a summary table.. Count of Months resources are allocated to a Project Type.

    Can you please help me.

  30. Hi Svetlana,

    I have been following your posts and find them very helpful.
    I just need help on something:

    I have a running list surgical procedures for 2014.
    Col A = date (running list)
    Col B = Types of Procedure (Procedure A, Procedure B,...)

    I want to count total number of procedures per month:
    January - How many Procedure A, B, C, D...?
    February - How many Procedure A, B, C, D... and so on.

    Thank you.

  31. I have following data sheets.

    sheet1data
    Branch Code Risk Number of high risk No of Risk Instances
    0101 H 04 18
    0102 M 11 20
    0103 H 9 24
    0104 L 5 13
    0105 M 10 26

    How I can fix formula that if branch code = 0101, check risk, count number of high risk and multiply number of risk instances

  32. Formula that will continuesly count based on Cell value and number it sequencially

    Col1
    ab
    cd
    ef
    ef
    cd
    ab

    Result
    ab-2014-01
    ab-2014-02
    cd-2014-01
    cd-2014-02
    ef-2014-01
    ef-2014-02

  33. HI there.

    I'm not well versed in COUNTIF, but I think this is what I need but not sure how to go about creating a formula.

    I have column with a $$ amounts in each cell and the corresponding column/cell would either be a Yes or No.

    I need to create a grand total of all Yes' and a separate grand total for No's.

    Can you help?

    Thanks in advance.
    Marcella

    1. =countif(A:A,"yes")
      =countif(A:A,"No")
      A:A which area you want to range selection data if you want.

  34. hi,

    i have a excel sheet where many of products like 20p,6P,2D
    and i want to sum of products in next sheet againts product when i filter any where in main product sheet

    1. Hello Chandan,

      I am sorry, I cannot follow you. If you can explain the task in more detail and provide a sample of your data, we'll try to help.

  35. Hi Svetlana, I've read most of these question and can't find one with the same problem as I'm having. I have a range, for example A3:A9, and I want a result when another cell contains text from one of the cells in the range. For example if A3="cat", I want a result when, for example, C2="a cat on a mat". If the situation was reversed, and I wanted to know if the contents of a cell was contained in a range, I'd do =COUNTIF(A3:A9,"*"&C2&"*"), but I want to know if any cell in a range contains text in a cell. My problem is I can't use add anything, like a wildcard, to a range, so I can't do =COUNTIF("*"&A3:A9&"*";C2)

    Is there a way to get this result?

    Thanks
    Ben

  36. I have a document where one column has list of name (around 100). Then I have so many other columns which can have any one value from set of 5 values(val1,val2, val3, val4, val5).
    Now I want to count number of val3 in row where name is Name12.
    Now to make is dynamic I have created 2 cells
    1. First to select Name (data validation = list)
    2. Second to select val (data validation = list)
    Now I want the next row to calculate total cell where value = value of second cell but from the row where name = value from first cell.

    1. Hello Amit,

      You need a VBA macro to fulfill this task. You can ask for it in special VBA sections on mrexcel.com or excelforum.com

  37. HI
    I PREPARED A WORKSHEET OF OUR EMPLOYEES TO CALCULATE SOME DATA SUCH AS THERE AGE AS ON DATE, DIVIDED THEIR AGE GROUPS ETC. BY GIVING DATE OF BIRTH. I CALCULATED THEIR AGE BY APPLYING =YEARFRAC(A1,TODAY(), 1) FORMULA. FURTHER I TRIED THEIR AGE BY DIFFERENT GROUPS SUCH AS 20-25, 26-30 ETC. FOR WHICH I APPLIED =COUNTIFS(C:C,"=CT",'G:G,">=21",G:G,"<=25"). BUT DID NOT SHOWING ACTUAL NUMBERS OF PERSONNEL COMES UNDER THAT CATEGORY IN SAID WORK SHEET. REQUEST HELP ME TO SOLVE THIS ISSUE.
    S/N RANK NAME DATE OF BIRTH AGE AGE 21-25

    1 CT MUNNA 18-06-1991 23 17
    2 CT MITHUN 28-02-1991 24
    3 CT BISWAJIT 26-06-1991 23
    4 CT KARTICK 14-10-1989 25
    5 CT SOURAV 22-10-1991 23
    6 CT VIKASH 28-02-1991 24
    7 CT VEERENDRA 03-01-1992 23
    8 CT OM PRAKASH 01-07-1989 25
    9 CT PAWAN 02-01-1990 25
    10 CT SANJAY 09-01-1990 25
    11 CT SUNIL 21-10-1990 24
    12 CT Atanu 10-07-1991 23
    13 CT KALI 05-02-1990 25
    14 CT RAKESH 05-01-1991 24
    15 CT SUNIL 09-02-1991 24
    16 CT PUPINDER 12-12-1991 23
    17 CT PATIL 23-11-1991 23
    18 CT Gulab 10-07-1991 23
    19 CT Bhupendra 18-12-1991 23

    ACTUALLY ALL ARE COMES UNDER 21-25 AGE GROUP BUT SHOWING ONLY 17 NOS.

    THANKING U

    1. Hi Satheesh,

      The correct formula is as follwos (F:F instead of G:G):
      =COUNTIFS(C:C,"=CT",F:F,">=21",F:F,"<=25")

  38. I am trying to count the number of occurrence of which the two certain conditions must be met. I tried it this way

    COUNTIFS('Brgy Cases,other Agencies'!$C$11:$C$13,"*Alangan*",'Brgy Cases,other Agencies'!$V$11:$V$13,"*Those Issued*") + COUNTIFS('Brgy Cases,other Agencies'!$C$15:$C$17,"*Alangan*",'Brgy Cases,other Agencies'!$V$15:$V$17,"*Those Issued*") + COUNTIFS('Brgy Cases,other Agencies'!$C$19:$C$21,"*Alangan*",'Brgy Cases,other Agencies'!$V$19:$V$21,"*Those Issued*") + COUNTIFS('Brgy Cases,other Agencies'!$C$23:$C$24,"*Alangan*",'Brgy Cases,other Agencies'!$V$23:$V$24,"*Those Issued*") + COUNTIFS('Brgy Cases,other Agencies'!$C$26:$C$27,"*Alangan*",'Brgy Cases,other Agencies'!$V$26:$V$27,"*Those Issued*")

    You can notice that there is 5 COUNTIFS function, and it's working fine. Unfortunately, when i am trying to add another set of countifs, the result returns a date format.

    Is it because there is a limit for summing up the occurrence in COUNTIF limited only to 5?

    Thank you.

    1. Hi Michael,

      A formula can contain up to 8192 characters, no matter how many COUNTIFS are used. Just change the format of the cell with the formula to Number.

  39. Is it possible to use the COUNTIF option when using fractions? I have a column of several lengths (32", 36", and 48"). I know the formula to count the cells - but I'd like to count the cells in the same column that only have 32" lengths in the cell. I tried the following and it did not work.
    =COUNTIF(A3:A25,32")
    =COUNTIF(A3:A25,32)
    =COUNTIF(A3:A25,"32")

    Thank you!!!!!

    1. Hi Becky,

      Try this formula:
      =COUNTIF(A3:A25,"32""")

      I know it looks very unusual, but it works : )

  40. Hello

    i need help to be able to use the countif function or similar to count text as 2 instead of just one is this possible

    thanks

    1. Hello Liam,

      Just multiply the COUNTIF result by 2, e.g. =COUNTIF(A:A,"text")*2 If you have any other conditions, please specify.

  41. Hi Svetlana
    I am trying to count the number of time a postcode is used within some 35,000 records. The postcodes have the format 'AB10 9BC' and I only wish to count the first group of figures. Using =COUNTIF(G:G,"AB10*")seems to work fine BUT.... Some of the first group of numbers consist only of three digits eg 'AB1 9BC'. This means that counting for AB1* also includes and counts AB10/AB11/AB12 etc etc. How can I count only AB1 postcodes in this example? Hope that makes sense and I look forward to your reply.

  42. Hi,

    I am trying to count the number of days marked "Holiday" in a set range but only those with a date less than 31/05/2014 but greater than 31/03/2014. The date range is in column R with the text fields in columns T:X.

    The other point is that the date column only has a week commencing date, not a date for every cell.

    I have tried =COUNTIFS($R:$R, "<="&I30,$T:$X, "Holiday")(where I30 is a date field with 31/05/2014). This produces an #VALUE! error. I haven't tried to enter the additional date criteria as I wanted to get the formula to work first.

    Taking each part in isolation (=COUNTIF($R:$R, "<="&I300 and =COUNTIF($T:$X, "Holiday")) works perfectly but not combined.

    Can anyone point me in the direction of where I'm going wrong?

    1. use formula in countifs best multiple condition apply one time

  43. i have a question, i am making a personnel planning for my employees. This is based on competences this means that specific people are trained to do job A but they can not do job B. I have 2 tables. One table "Training" with the name of the person and in the same row for which jobs they are trained so job A, job B, job D. Then i have the real personnel planning. I have on top which job needs to be executed so for example job A. Hence I put a name of the person. I want to have the cell highlighted in Red if the person is not trained for job A. I think to do it via COUNTIF but i am confused as I have different criteria, i need to refer first to job A, hence i need to search if that specific person is trained for job A. Can somebody help me please?

  44. Hi, for my case, cell A1 has an entry of 19/11/14 and cell B1 has an entry of 25/11/14. 25/11/14 is more than 19/11/14 by 6 days. I would like to set the formula for the dates that are more than 5 days to be false. Which means the entry above is false because it is 6 days.

    1. And for the entries that are less than or equal to 5 days to be True.

  45. I have only one column
    how to count how many row have time 7 am to 8 am and next 8 am to 9 am and next?

    7:31:11
    7:48:41
    7:51:35
    7:59:49
    8:02:01
    8:03:47
    8:06:41
    8:07:16
    8:07:47
    8:15:20
    8:19:43
    8:29:16
    8:33:42
    9:09:05
    9:09:36
    9:11:56
    9:19:27
    9:19:55
    9:20:25
    9:26:13
    9:34:33
    9:35:19
    9:47:05
    9:49:42
    10:10:10
    10:10:24
    10:10:37
    10:10:59
    10:11:10
    10:11:22
    10:11:43
    10:12:07
    10:12:29
    10:12:42
    10:13:04
    10:13:25
    10:14:05

  46. Dear All

    I need urgent help on one formula. For Eg

    A B C D E
    AXT-11232 2,500 89369 sent

    I want in column E the current date if any of the cells like A, B, C or D are filled. My data will be in the same format as in A, B, C. I just want the current DATE , whenever data has been entered in any of the cells marked as A, B , C or D

  47. I like to count First five character(number) as a individual number in a column but in different rows and same numbers count once only. How can I do this in excel? Fore example;

    A B
    98765_A1A2A3 ISO
    98765_A4 AATCC
    87654_A2A3 AATCC
    92345_A1A3 ISO

    1. =LEN(ABS(LEN(E3)+SUBSTITUTE(LEFT(E3,5),E3,1))-15)

  48. I like to count First five character as a individual number like; 98765_A3(98765),98765_A6(98765),87654_A2,(87654),92345_A1(92345) in a column but in different row and same numbers count once only. How can I do this?

  49. Hello,

    My countif formula returns 0 if it does not anything that matches the criteria. Is there a way to set up the formula so that if it doesn't find anything that matchs the formula returns "-" instead of 0.

    Thank you

  50. Hi! Here's the data I want to process. I want to count the number of entries a specific person submitted that matches anything except Not Qualified.

    DECLINED Peñalosa, Joyce Ann
    NOT QUALIFIED Calvo, Vivian
    CALLBACK Roda, Scepter John
    NOT QUALIFIED Henardino Jr., Edwin
    NOT QUALIFIED Asentado, Darell
    CALLBACK Peñalosa, Joyce Ann
    DECLINED Roda, Scepter John
    CALLBACK Alipio, Marisel
    DECLINED Emeterio, Absalon
    NOT QUALIFIED Solomon, Cinderella
    NOT QUALIFIED Solomon, Cinderella
    CALLBACK Toring, Jared
    CALLBACK Henardino Jr., Edwin
    NOT QUALIFIED Viscayno, Mabel
    NOT QUALIFIED Viscayno, Mabel
    DECLINED Solomon, Cinderella
    SCHEDULED Roda, Scepter John
    CALLBACK Emeterio, Absalon
    NOT QUALIFIED Henardino Jr., Edwin
    NOT QUALIFIED Trocio, Princess Joy
    CALLBACK Baruel, Coleen Grace
    CALLBACK Baruel, Coleen Grace
    CALLBACK Baruel, Coleen Grace
    NOT QUALIFIED Toring, Jared
    SCHEDULED Solomon, Cinderella
    SCHEDULED Gamboa, Estrella
    DECLINED Peñalosa, Joyce Ann
    DECLINED Calvo, Vivian
    NOT QUALIFIED Alipio, Marisel
    CALLBACK Baruel, Coleen Grace
    CALLBACK Arizobal, Argin
    DECLINED Calvo, Vivian
    NOT QUALIFIED Arizobal, Argin
    CALLBACK Toring, Jared
    DECLINED Emeterio, Absalon
    SCHEDULED Peñalosa, Joyce Ann
    NOT QUALIFIED Henardino Jr., Edwin

    1. Hello Shwan,

      Here you go:
      =COUNTIF($B$1:$B$100, "Person Name") - COUNTIFS($B$1:$B$100, "Person Name", $A$1:$A$100, "Not Qualified")

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)