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 53. Total comments: 2074

  1. Hi,
    Can you send me the sample excel, how to calculate & I need the count of net working day

    Example: In a monthly if I used to get "n" number if volumes... I want to calculate the ageing.... But I don't want to calculate Saturday & Sunday...

    Can you support me to get the the format sample through mail

    1. =NETWORKDAYS.INTL(I6,I36)
      I6 - Start Date
      I36 - End Date
      Auto Show How many date working

  2. Hi any one can help me on the following
    Peter 02/01/15
    Hari. 05/01/15
    Sam. 12/01/15
    Peter. 13/01/15

    =countifs(a:a,Peter, b:b,"="&today-3) but I suppose to get 1 ryt if today date is 13/01/15??

  3. I have a problem regarding counting multiple criteria and the problem is counting only the "year" my data date like 29/05/2014.

    =COUNTIFS('Rabbit Inventory'!$F:$F;"Buck";'Rabbit Inventory'!$E:$E;$AG5;'Rabbit Inventory'!$Q:$Q;"Purchased"; 'Rabbit Inventory'!$R:$R;YEAR(2014))

  4. Hi, Firstly, I thank you for providing this excellent service. You are doing a great service.
    I need some help with this. I want to send/link cells or rows or columns to another worksheet/book when the columns meet certain criteria.
    For example, if the date and time attended in column D and E is later than a day or 10 hours compared to column B and C, and link those data that match criteria to another sheet.
    Thank you and hope to hear from you soon.

    Regards.
    Joseph

  5. Hi Svetlana,

    Please help me get a formula for my query..

    In an excel sheet having 3 columns- 'Date', 'Time' & 'Amount'. I need a formula which will add only those amount in the 'Amount' column, where the Date is from A to B & Time between and equal to C & D.

    Please can you help me.?

    1. Just to add to above query, A & B are 2 different dates, and C is a time in A & D is a time in B.

  6. Hi,

    please help me count the same value from different cells in the same row. The cells I need to be able to choose.

    What I need to calculate is the attendance for each student but on two different occasions. So I will have to choose every second cell and thus it can't be a range.

    Sun. Dec 21 Mon. Dec 22 Tues. Dec 23 Wed Dec 24 Thurs. Dec 25 Attendance
    RW CS RW CS RW CS RW CS RW CS
    P P P A A A P P P P 0
    P P P P P P P P A A 8

    As you can see, it is Communication Skills and Reading and Writing Skills that the student attends on two different times.

    I tried

    =COUNTIFS(D8,"=P",F8,"=P",H8,"=P",J8,"=P", L8,"=P")

    and many other formulas with no success.

  7. Svetlana Cheusheva you are so beautiful

  8. Thanks for Guide.

  9. I am trying to use the COUNTIFS formula

    I want to count the numbers of different interest rates in one column and produce a result if it satisfies the criteria that it belongs to a field called MN (there are various other fields..

    eg COUNTIFS(('PD11'!$H$8:$H$177,"<6%"),('PD11'!X8:X177,"=MN"))

    Help!!

  10. Hi Svetlana,

    I have two strings FirstRow & LastRow defined as address ranges

    How can I use CountIFS formula to count number of zeroes between these two ranges.

  11. Hello Miss Svetlana Cheusheva,
    I want ask you about How do I Sum or ets in one cell. I have 2 cell (A1)(A2), in cell one(A1) have number that I enterd, an other cell (A2) I have condition is that: if A11.000.000*3. It means between 2 Numbers that I imagin

    1. I want function alike this: =if (A11.000.000;A1*3;"No Number")

  12. Is there a way to count the number of instances in a column based on a criteria and then display the results not as a number, but as a list of data from the cells that meet the criteria?

    Such as:
    Lenovo User 1 Serial#
    Lenovo Inventory Serial#
    Apple Inventory Serial#
    Apple User 2 Serial#
    Apple Inventory Serial#
    Microsoft User 3 Serial#

    =COUNTIFS(A1:A6,"Apple",B1:B6,"Inventory")

    But I would like to display the rows where the data meets these criteria, instead of the number of instances.

    Desired display in a separate part of the spreadsheet, while not deleting the original cells.
    Apple Inventory Serial#
    Apple Inventory Serial#

    Thank you very much for any assistance, positive or negative.

    Timothy Scully

  13. Hi i need to count values in a colum condicioned by another colum, just like a i have XX,YY and ZZ in colum A and Values in Colum B i need to say cout at colum B if at colum A is YY how can i do it? Need help

  14. Hi There I wonder if you can help me with COUNTIFS for text Values:

    I have NATIONALITY in row G, and TYPE OF VISA in row J,K,L,M,....(contains "1"/symbol "x" as value).

    I use =COUNTIFS(G12:G117,"=NATIONALITY",J12:J117,"1",......) and it works.

    now if i want to apply the formula to a new items, how is the fastest way to do that?

  15. I am trying to count the number of Army in one column that is due to depart between a date range of 90 days in a separate column. I have tried =COUNTIF(I:I,="ARMY")+COUNTIF(Y:Y,">="&TODAY()+1)-COUNTIF(Y:Y,">"&TODAY()+90). I get the correct number for the dates in the range, but doesn't exclude Air Force, Marines, and Navy. I hope you can help. Thank you

  16. This time with name...
    Hi!

    I need to be able to count the number of columns that have a date that is less than 11 days old. I understand I could do this using the Today() function, but it keeps hanging. I currently have =COUNTIFS(Infrastructure!$A$2:$A$2000,"Infrastructure",Infrastructure!$C$2:$C$2000,"<11"(&TODAY()-$C2)
    Where infrastructure is the name of the tab to go to, the first criteria says if the cell is 'Infrastructure', then the problem is the last calculation. I want to count the number of occurences where today minus the date in cell c2 is less than 11. CAn you help?

    1. Hi Jennifer,

      If my understanding is correct, you need just need to tweak the formula a little bit:
      =COUNTIFS(Infrastructure!$A$2:$A$2000,"Infrastructure",Infrastructure!$C$2:$C$2000,">="&(TODAY()-11))

  17. I am trying to count the amount of cells that list "Baltimore" in column H and the Agents name in column F. So basically how many lines in a sheet that contain a name AND a particular client.

    I tried =COUNTIF(Eval_Data!F:F,BRL_Agent_Summary!C4)+COUNTIF(Eval_Data!H:H,”Baltimore”)

    I fount that this will count the agents name but every instance that agents name appears and also if Baltimore appears.

    I also tried =COUNTIFS(Eval_Data!F:F,"C4",Eval_Data!H:H,”Baltimore")

    But it isn't counting the cells I want. It is bringing back 0 when I know there is data. I'm not sure what is wrong.

    1. Hi Joanna,

      Try the following formula:
      =COUNTIFS(Eval_Data!F:F,BRL_Agent_Summary!C4,Eval_Data!H:H,"Baltimore")

  18. 1202 13
    1401 110
    2107 121
    1108 140
    1111 154
    1103 210
    2102 812
    2103 1103
    1208 1104
    1014 1105
    1107 1111
    1105 1154
    2308 1204
    1206 1210
    n/a 1231
    1154 1401
    2305 2101
    1306 2102
    2101 2103
    1204 2107
    1201 2201
    1210 2302
    2302 2308
    1104 (blank)
    1106
    2104
    1301
    1303
    1402
    2201
    2309
    1205

    I have two column's of number.
    Column1 is actual numbers
    Column2 is numbers that have been input manually

    I want to use a countif function to tell me how number in Column2 are not in Column1

    1. Hi Richard,

      The task requires creating a helper column, say Column C. enter the following formula in C1 and then drag it down to other cells:
      =IF(AND($B1<>"",COUNTIF(A:A,$B1)=0), "Not in Column 1", "")

      After that, you can use the below formula to count how many numbers in Column2 are not in Column1:
      =COUNTIF(C:C, "Not in Column 1")

  19. I am working on a spreadsheet with a basic countif formula thats working well for me. When I add a filter to the worksheet in column A and filter by one item (e.g Apple) the formula does not autmatically updated. Please help.

    1. Hi Sham,

      The point is that the COUNTIF function processes all cells within a specified rage, not only those that are visible at the moment.

  20. Hi
    I hope you can help me. I am trying this formula as follow.

    =COUNTIFS('Sheet1'!$AV:$AV,""&"",'Sheet1'!$AR:$AR,"*Feb*") +
    COUNTIFS('Sheet2'!$AV:$AV,""&"",'Sheet2'!$AN:$AN,"*Feb*")

    I need to know contacts per month. If AV is not empty and AR in sheet1 / AN in sheet2 have a month, count it( they could be empty, so count only the ones that have months on them ). As simple as that.

    It doesn't return any value when I use ""&"", or "*".
    The format of both is "text" and the range as is the same.
    It is not working and I can not see what I am doing wrong.
    thank for helping!!

  21. 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)

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

  23. 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!

  24. 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")

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

  26. i have 500 account number
    how to search double

  27. 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!

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

  29. 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?

  30. 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! :)

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

  32. 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?

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

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

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

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

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

  38. 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!

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

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

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

  42. 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)

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

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

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

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

  47. 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?

  48. 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" "'".

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

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

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 :)