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 43. Total comments: 2055

  1. I have data in Column F. The data is like either "M" or "MH", either "E" or "EH", either "N" or "NH". This is for counting Shifts.

    I am using formula to count : =COUNTIFS(F4:F30,"=E",F4:F30,"=EH") for counting Evening Shifts. But it is givining me value as 0 (Zero). Please let me know where am I wrong.

    Thanks for your help in advance.

  2. I'm trying to get a total count of the values in one column, i.e. "Y100" and with all rows that contain that y100 count the number of cells in another column. i.e. "A". I've tried =COUNTIFS('Mods (Entire)'!B:B,"Y100",'Mods (Entire)'!C:C,"A") and =COUNTIFS('Mods (Entire)'!B:B,"=Y100",'Mods (Entire)'!C:C,"=A") both come back with a count of zero. Any and all help is greatly appreciated, will send sample if needed.

  3. From the example above, if you use the following:

    =COUNTIFS(B2:B11,"=0", C2:C11,"=0", D2:D11,"=0")

    it will count from the row only if all three meet the criteria

    however if you do:

    =COUNTIFS(B2:B11,">0", C2:C11,">0", D2:D11,">0")

    It will count from the row if any one, two, or all of the values is greater than 0

    q1: how is that logical?
    q2: How do I do the operation so that it counts only if all three satisfy the criteria

  4. help on how to set COUNTIF to count only cell with >=25% but <=29% only,, meaning count only cells when it contains 25% and below 30%

  5. I am trying to figure out a formal that would highlight a certain cell if a X appears a certain number of times within a certain data range. I am trying to work on an attendance sheet where i need the persons name to highlight if the attended 3 items in one data range and 2 in another data range and have data in another field that contains a drop drown list. I think i need to do a sum countif but I can't figure out how to set to highlight if a certain number within the data field is meet.

  6. I want to count how many nonzero cells exists. All cells are time formatted. I am populating with elapsed time.
    Column A - 0:00
    Column B - 8:05
    Column C - 9:22
    I should get result as 2.

  7. I am working with something like this:

    COLUMN A COLUMN B COL.C COL.D
    load# trailer# scac loc
    uc856489 85694 SXTI y182
    54852 SXTI y345
    uc854585 95484 TAMI y652
    45584524 6382 TAMI Y212
    54585 SXTI Y121
    UC845845 12548 JRWS Y222

    I'm trying to find a rule that will let me see if there is get a count of empty trailers on the yard by scac code "SXTI" (COL C) BUT I don't want to calculate the loaded SXTI trailers (WHEN COLUMN A SHOWS A 3 STARTING WITH UC* OR 455) I was going to make a line for each scac code on a different sheet with the rule cell next to it just showing the number. Is there a way that will work for this? I have tried many combinations but can't seem to get it to work.

  8. Hi,
    Can you please help?
    Example:
    - M M N N
    M V V D D
    M V V V
    N D V D E
    N V E E
    WE need do know how many "V" we have in the table with the conditions (Horizontal/Vertical) M/N ; M/N ; N/M ; N/M
    The result is:
    M/N = 1
    M/M = 4
    N/M = 2
    N/M = 0

    Many thanks in advance.

  9. =COUNTIFS(C12>F12,">=1", C21>F21,">=1") ???

    Basically looking to have a total for wins and losses in a sports results column.

    So if 71 (C12) is greater than 70 (F12) give it a value of 1 (a win), then add that to the next line (C21) if that is a value of 1 as well.

  10. I have 5 columns,
    1. Date In
    2. Time In
    3. Date Out
    4. Time Out
    5. Date to count
    I have a long list of these dates and times and want to find out by date and hour how many occurrences.

    7a-8a
    8a-9a
    9-10a etc.

    How do I assemble countifs to produce the hourly data?

    Countifs(A1:A31,E1,B1:B31,">="&TIMEVALUE("7:00 AM"),B1:B31,="&TIMEVALUE("7:00 AM"),D1:D31,<="&TIMEVALUE"("8:00 AM")

    I can't get to this to work so it shows between the in and out times and date.

    Thank you,
    Mike

  11. HI,

    I HAVE A EXCEL SHEET CONTAINS PERVIOUES HEALTH CHECK UP DATES AND I HAVE CREATED GRADES ACCORDIGNLY,
    WANT TO FIX DATE FOR THE GRADES AS MENTION BELOW
    IF GRADE A MEANS HAVE TO GET THE DATE OF NEXT HEALTH CHECK UP FROM PREVIOUS CHECK UPDATE WITH 180 DAYS ADDED

  12. hi dear i dont have knowledg of excel formulas so plz guide me how learn a advance excel formula.
    and i need your advice.

  13. I would like to use a countif function in a pivot table. I have four columns of data with different suspension codes in each column. I might have violation "I01" appear in any of the four columns. I want to count how many times "I01" is in any of the four columns. I love the pivot table because then I can use them to count how many times this infraction is reported at each of the schools I work for. Any suggestions would be appreciated.

  14. Hello,
    This is my current formula for two different columns: =COUNTIF(B12:AF12,"lt") and =COUNTIF(B12:AF12,"d").
    I am tracking Time, Attendance and other things for employees. If they have a late on one day (cell) I put LT and it counts it in the LT Column. If I put D for Disciplinary action it will count it on the D column but what if I have a late and Disciplinary action on the same day? I want it to count it on each different column and cell needed.
    Thank you,

    Eric

  15. In Example 4. COUNTIF formulas with OR logic is there a reason you did not use the sum of the array?
    =SUM(COUNTIFS(A2:A11,{"Product1","Product2}, B2:B11, 0))

    I am also wondering the best way to count rows with multiple OR logic? I know the following doesn't work with 2 columns of OR logic, but what would be the best way to accomplish this?
    =SUM(COUNTIFS(A2:A11,{"Product1","Product2}, B2:B11,{"0","14"}))

  16. I've hit a brick wall with sheet that I am trying to Create to create a rota from a list of tasks that need to be performed at different times of the day. I have a list of tasks that must be performed and a person's name is input against it and the name is the automatically transferred to the rota by the formula.

    I have set up the rota so that the cell under a specific time and persons name tests if the person has been allocated the task with the formula =IF(F8="Jane","task1","")
    so that when Jane looks across the rota against her name she can see the tasks that have been allocated to her. This is working perfectly.

    The problem is that at 12:00 Jane might be allocated any one of two tasks that need to be completed at the same time or no task at all. I have been trying to use the above formula and adding a second If statement onto it to test another cell if the first statement results in FALSE.
    =IF(F8="Jane","task1",""),=IF(F10="Jane","task2","")
    and this is not working.

    Can you help?

  17. Hi,
    I need to simplify no of item for selected branch in a month? or to combine below countif formula.

    1. TO COUNT NO OF ITEM IN JULY 2015
    =COUNTIF(I:I,">=07/01/2015")-COUNTIF(I:I,">07/31/2015")

    2. TO COUNT NO OF ITEM FOR BRANCH CODE "AG"
    =COUNTIF(A:A,"=AG")

  18. I want to compare two rowed cell values and find minimum between both,Minimum result number will be multiply by 3.5 and stored to third cell.

    10
    9
    9*3.5

  19. I'm trying to calculate how many times a state appears in a range. =COUNTIF('[2State Calendar.xlsx]Calendar'!$J$6:$K$11,"*NY*")

    I want to calculate multiple states appearing in the same range. Is this possible?

  20. Oh dear - that did not post correctly!

    The first formula is =COUNTIFS($A$2:$A$10000,">=2420", $A$2:$A$10000,"="&$H2,$B$2:$B$10000,"<="&$I2) (where h2 and I2 are dates) and b is a column of dates

    Hopefully that helps clarify.

    Thanks,
    Jo

  21. Hi,

    I need to count the number of postal codes within a certain range, e.g. between 2420 and 2490. I have been using the countifs function, however it does not seem to be counting all the postcodes that exist within the range specified (=COUNTIFS($A$2:$A$10000,">=2420", $A$2:$A$10000,"="&$H2,$B$2:$B$10000,"<="&$I2) - where h2 is a date and so is I2). I have also found that this formula is not counting all the dates that fall into this range in column b. Is there something also wrong with this formula?

    I would appreciate any help I can get!

    Thanks in advance,
    Jo

  22. True, Microsoft Excel 10 is the value of the F column. 6 When the value is put in the column H 4 value in column G to be put. I have required this type formula.

  23. True, Microsoft Excel 10 is the value of the F column. 6 When the value is put in the column H 4 value in column G to be put. Examples of this type of formula.

  24. Hi,

    Good day!

    I was hoping you could help me on this, I need to create a statement comparing values only in one column. If there are duplicate values, the first value will be tagged as "primary" and the succeeding "secondary"
    Is this possible?

    Thank you in advance!

    Kind regards,
    ejoy

  25. which formula used for the below table

    Book stock Physical stock Difference stock (+)stock (-)Stock

    Physical stock (-) Book Stock is difference stock, if difference stock comes (+) go to (+)Stock otherwise comes (-) go to (-) Stock

    how to use the If formula, explain clearly

  26. Hi Alexander

    Have send the Sample Workbook for your ready ref.

    Kind regards

    Rishi Bansal

  27. Hi,
    I am struggling with my data in which I have a list of people with their ratings (ranging from 0 to 5) the past 5 years and I need to separate the people who had at least a rating of 4 twice during the past 5 years.
    I tried a combination of IF and Countif but can't seem to get it right. Your help would be highly appreciated.
    Thanks

  28. Hi,

    I would like to ask for some assistance in the COUNTIF formulas. I have a comprehensive list which gets fed information from another spreadsheet. It looks in these cells for the word(s) im looking for.Now in the same cells for each one, i'm looking for about 100 different words which means i need to write the "word" in every formula.The cells are separated and cant be group-selected Overall due to my spreadsheet and how broad it is for what I want and need, ill need to change the word in the equation approximately 1600 times. Not really in the mood to do that if possible.

    Heres the formula example Im using right now. Can I reduce the size of this formula and make it more concise. If you want me to send an example spreadsheet, im willing to do so.

    =COUNTIF('Group A'!E6:I6,"")+COUNTIF('Group A'!P5:T5,"")+COUNTIF('Group A'!E13:I13,"")+COUNTIF('Group A'!P14:T14,"")+COUNTIF('Group A'!E21:I21,"")+COUNTIF('Group A'!P22:T22,"")

    Thanks :)

    1. Hello Liam,
      Yes, please send your sample workbook to support@ablebits.com and we will try to help you.

      1. Thanks for looking. Ive sent that address the spreadsheet example.

        Thanks again for your assistance

  29. In column Q, I need a formula that will count all of the cells that have ROH,EXEC,and STAFF. I tried using the format below but it did not work.

    =COUNTIF(Q11:Q275,"ROH") + COUNTIF(Q2:S11,"EXEC") + COUNTIF(Q11:Q275,"STAFF")

    1. Hello Melony,

      In your formula, the second COUNTIF contains a different range, and this may be the cause of the problem.
      Try the following formula:
      =COUNTIF(Q11:Q275,"ROH") + COUNTIF(Q11:Q275,"EXEC") + COUNTIF(Q11:Q275,"STAFF")

  30. Hi Svetlana,

    I have read your comments.

    I have a data which a row consists of both Text (Y & N) and Dates (29/09/2015).

    I want to count the number of cells that contain both Y and date but donot count N.

    Please suggest me a formula to count number of cells.

    1. All Y , N, Date are in different cells.

  31. Hi

    I am facing a problem to plot the value against "Logical_Text", Wherein Same "Logical_Text" are in Col. "A" (in multiple rows) and A Value has been given against one of the "Logical_Text" in Col. "B". I need to plot the Value given (in Col. "B") against the "Logical_Text" in Col. "A" on all the rows against "Logical_Text" in Col. "A".

    Please advise the action and formula for doing so.

    Thanks & kind regards

    Rishi Bansal

  32. Hello!
    I am having an issue related to this article that I just can't seem to solve.
    I get an excel dump every week with all cases handled by a support organisation.
    My job is to summarize the number of cases created by a member of a specific group of people
    I am also supposed to summarize the ammount of cases that have been created by one specifik user and where the case is assigned to a specifik case group.
    The file is in 4 collumns. Collumna A is case ID which is not interesting to my assignment.
    Collumn B is the name of the case group that the case is listed under.
    Collumn C is the time the case was created
    Collumn D is the name of the person that created the case

    Collumn D can contain up to 50 different names but I need to count the ammount of cases related to a specifik list of 12 names.
    I need to count them per day so between midnight one date and the next.

    Can this be done using a set formula, so that I can just paste the new excel dump in to a set worksheet and get the informations directly?

    Would very much appreciate your input.
    Best regards
    Andreas

    1. I can send an example by email if you want. Just write me on

      andreas . funk /at\ umea . se

      Thanks!

  33. Jamaica
    Jamaica
    Hi, so this is my data:
    USA
    Jamaica
    Bulgaria
    USA
    France
    Jamaica
    Russia
    USA
    USA

    and the question asks to use the IF or COUNTIF function to find the number of countries (which is 5.... but what is the formula?)

  34. how to enter only 2 item in a CELL in ms excel 7. EX - IN A CELL ONLY YES / NO --- CAR / BUS

  35. Hello Svetlana,

    Hope you are doing great.
    I would like to know how to know if there are multiple combinations of two rows.
    For Example:
    Column 1 | Column 2
    ------------------------
    A | value1
    A | value1
    A | value2
    B | value3
    B | value4
    C | value5
    C | value5
    D | value6

    Here 'A' from Column 1 has multiple values in Column 2, namely 'value1 and value2'.
    Similarly 'B has 'value3 and value 4'.
    Suppose there are thousands of such records.
    And I would want to know which elements in Column1 has different matches in Column 2.

    Could you please help out on how to get this.
    Any help is much appreciated.

    Thank you,
    Shashi Bhushan

  36. Greetings!
    Whilst adding attendance using countif function if P = 1 shift and the person continues for the second shift which is marked as 2P = 2 how can the two be added? ie if there are 30 days and the person has worked 1 shift extra it should count as 31 Thanks Murali

  37. Hi Svetlana,

    Could you please help? I am attempting to count a different range of between numbers and assign them a score if they hit the specific criteria.

    Example.
    Monthly Unit Sales 100 - 500 = 1 point 500 - 1000 = 5 points 1000+ = 10 points.

    Curious to what formula I can apply as there are at least 8 columns requiring the same formula & I then need a total score when all columns are combined.

    Thanks
    James

  38. Hello Svetlana,

    I need help analyzing data in Excel. I am working on my thesis and the data was sent to me from a program via Excel.

    1. I saw the how to create a drop down in excel. How can I convert the numbers in the excel file to for example yes = 1 and no = 2?

    2. Once I am done with that portion, do you have a link where I can analyze the information?

    Thank you

  39. hi there. hope you can help. i have table of 2000 lines. i have created count formulas to go over the entire data...=COUNTIF(J5:J4000,"=2")
    issue i have is that i want to break it down per person (far left hand column) then still use the same formulas to get the same formula but only for the person as well as the whole 2000 lines data.

    in the 2000 line data, there will be approx 15 names that i need individual results from... hopes this makes sense and you can help

    PERSON JOB NUMBER CALLS MADE TODAY
    BILL 432 1
    BILL 4 1
    BILL 34 1
    BILL 325342 2
    BILL 5 2
    PETER 342 2
    PETER 5432 2
    PETER 5 2
    PETER 52308 2
    PETER 432 2
    PETER 5 2
    PETER 432 2
    PETER 5 2
    STEVE 3425 2
    STEVE 3425 2
    STEVE 434 5

  40. Hi Svetlana
    I am attampting to count repairs done by tool type (column A), serial number (column B), and technician (column C). I've tried this and it doesn't seem to be working:
    =COUNTIFS(B3:B25,AA17,C3:C25,"<=2945",F3:F25,AA10)

    AA17 is a specific tool type
    AA10 is a specific technician

  41. I want to search an array where one value is an exact match, and the second value falls within a range (specified by two columns – an upper and a lower bound)

  42. Hi Svetlana,

    I am attempting to count rows that contain a specific piece of text in the C column as well as a specific number in the I column. The formula I am attempting to use is:

    =COUNTIFS(Sheet9!C2:C163,"="&B3,Sheet9!I3:I163,"=7")

    The formula returns a #VALUE! error but I am unable to locate it. Would you please point out what I am doing wrong?

    Thanks.

  43. Hi team,

    I had a query about countif and sum if and I have sent an email to the support email ID. I just want to know how long does it normally take to get a response.

    cheers

  44. wow thanks, i know now how to use count-if

  45. Is there a way to calculate the amount of hours in the same cell if I inputted 8am-4pm? the goal is to use one row to input the time as shown above and a row to show the total amount of hours. in this case would be 8.

  46. Hello

    I am trying to use a countifs combined such as shown below. What I am am trying to do is with to check each of the cells referenced in the range to see if the text in the criteria is present. Overall intent is to determine a percentage of compliance. So in this case if each of the cells referenced are determined to be met in the criteria check then the cell where this formula would be 100%. If only part or none of the criteria are met then the cell would be the percentage based on the counts. I have tried adding an If before the count but it seems something is wrong.

    =(COUNTIFS(BA5,"2008 or Above")+COUNTIFS(BA5,"Not Applicable")+COUNTIFS(BC5,"Has Maintenance",BC5,"Not Applicable")+COUNTIFS(BR5,"All Physical Servers Have Maintenance",BR5,"Not Applicable"))

    thanks for your time. Enrico

  47. Hi Svetlana,

    I am having a problem with countif function. I want to test a column data with dates and count them by the weekdays. Here is what I wanted to do, R18=COUNTIF(B3:B12,WEEKDAY(B3,1)=Q18). It is not working.
    How to count a column data by testing those datas with another function(Weekday)?

  48. 2-2-2= 6
    2-2-2= 6
    6-4-6= 16
    4-4-8= 16
    2-4-2= 8
    4-4-4-2= 14
    how ca we add these items and get sum

    1. If you simply want to some cells row-by-row, you can use a usual SUM formula, e.g. =SUM(A1:C3)

  49. Hello,
    I want to count the number of concentration values in a specific range (4,000) for a subject list. However,each subject (listed by patient ID) can have 2-5 concentrations (each listed in a separate row), and some are in more than 1 range (e.g. low and high). I would want to group by patient ID, keeping all their samples together, but sort the data into those patients with all samples being in the low range, all in the high range, all in the middle range, and then another group with those that are mixed ranges.

    Any help you could offer would be greatly appreciated.

  50. how can i count 2 categories in same raw ex n , b
    n b n b n b b b b b n b n b n b n b n b

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