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

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

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

  3. 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"}))

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

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

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

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

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

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

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

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

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

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

  14. Hi Alexander

    Have send the Sample Workbook for your ready ref.

    Kind regards

    Rishi Bansal

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  39. Svetlana, I've got a workbook where I'm tracking attendance at various classes. Each class and attendance roster is a separate tab. I've got an employee summary tab where I've set it up to count the number of times each employee appears across the various tabs. I'm using this formula =SUMPRODUCT(COUNTIF(INDIRECT("'"&$A$2:$A$15&"'!A1:M200")A29)) for that and it's very successful. A2:A15 are the tab names, A1:M200 the range on each tab to search and A29 is the employee name. So far so good.

    Now I need to do a similar count, but only when the class is required by the person's title. I had to set up the names of the various tabs (see above) in order to make the previous formula worked. So I just made it a grid by adding titles across the top and indicating which class was required for each title. I'm just lost now on how to make that work with a formula. Any help is appreciated.

  40. I'm trying to create a countif formula to avoid duplicate records. Two of my headings are "Name" & "Date" my goal is to NOT enter a name using the same date more than once. Can you help?

  41. Hi Svetlana -

    I was wondering if you could help me on how I can use Countif or Countifs to check a list/colmun of different words, tell me how many times it appears in a search of about 300 rows. I have about 70 different words to search in it would be time consuming if I just simply worked one at a time using =countif(A1: LP12,"Different Words to Look up").

    I believe there is a formula that I can just either drag and use to look all at onces.

    Thanks,

    Javier

    1. Hi Javier,

      If you have a list of those 70 words in some column, you can reference the first cell in the formula, say $A13, and then copy the formula down to other cells:
      =COUNTIF($A$1:$LP$12, $A13)

  42. Below is my formula, it is counting everything in the first () and not the second. Please help.

    =COUNTIFS('513TH BYNAME'!G2:G1500,"09L5",'513TH BYNAME'!H2:H1500,"OO")+ COUNTIFS('513TH BYNAME'!I2:I1501,"9J,9Y,9T,9V,9R,9Q",'513TH BYNAME'!K2:K1501,"*2015")

    Does not count the second COUNTIFS('513TH BYNAME'!I2:I1501,"9J,9Y,9T,9V,9R,9Q",'513TH BYNAME'!K2:K1501,"*2015")

  43. Hi,

    I want to count the number of cells from "today's date" to a specific charater, lets say S.

    For example: If todays date where 15:th of july and I have my sheet like this:

    Dates:
    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
    S P T L L

    I want it to count the number of cells, regardless if the cells are blank or have a different character(P,T or L) from 15th of july until it finds the first S, thus 15-3=12.

    Hope you can help.

    Thanks

    1. Somehting went wrong when I sent the question as seen above, it would be: 15-1=14.

      Please help

  44. Hi there,

    I have to get 2 columns of data from a data sheet.

    Basicly Column X is a name and Column Y is an answer of one of six results.

    While Column X will remain the same I need to see how many of each of the results appear for Coloumn y of the 6 criteria.

    I used =COUNTIFS(A1:A941, "JOHN", C1:C941, "CLOSED")

    But it wont tell me how many of the columns under Johns name are closed

    Can you help?

  45. Hi Svetlana,

    I'm using excel mac and I'm trying to countif a date range and using a separate worksheet the name of the worksheet is KIDS and the column in that worksheet is R. Please where am I going wrong, below is an example;

    =COUNTIFS(KIDS!R:R, ">01/07/2015", KIDS!R:R, "<31/07/2015")

    Thanks heaps :)

  46. I am working with excel to draw up characters in columns consisting of random alphanumeric combinations. For ex: TRG, 0RG, 12TT, in column organization. I want to list the number of each individual character and how many times the character appears within each column. .. IE: 0=1, R=2, T=3. The countif function completely works for alphabet characters, but not for numeric when next to alpha.

    The 0 numeric when beside an alphabet character is simply not recognized. I've attempted some * with no luck..I'll keep reading could you please provide some insight?

    1. Hi Andrea,

      You can use the following array formula (Remember to press Ctrl+Shift+Enter to enter it correctly):

      =SUM((LEN(A1:A100) - LEN(SUBSTITUTE(A1:A100, 0, ""))))

      Where 0 is the digit you want to count.

      Instead of supplying a numeric value in the formula, you can place it in some other cell, then the formula will be slightly different, as in the following example: Array formula to count any given character in a range.

  47. Svetlana Cheusheva,hope yo helping me,thanks

  48. YES NO
    x
    x
    x
    100%

    hi,how can I automate, such that when is placed in a NO column then the percentage reduces eg

    YES NO
    x
    x
    x
    67%

    regards

  49. Outlet type: A, B, C, D

    Expected Product range by outlet type

    A - 20g,30g,170g,410g,400g tin,400g pouch,uht fc,uht lf
    B - 20g,30g,170g,410g,400g tin,400g pouch
    C - 20g,30g,170g,410g
    D - 20g,30g,170g

    Product presence report (Yes means available while No means not available)
    Peak 170g - No
    peak 410g - Yes
    peak 30g - Yes
    peak 20g - Yes
    peak 400g Tin - No
    Peak 400g P - No
    Peak 900g - No
    Peak uht fc - No
    Peak uht lc - No

    Hi Svetlana, i have above merchandizing report data. Based on the outlet type and the lists of products expected there and the product presence report, i want a formula that will count and return the number of products present in the outlet by considering the type of outlet (A,B,C,D). Thanks

    1. Dear Svetlana,

      Still waiting please, i believe you are working on my request. Thanks

  50. i have got data of employees for a mining company. i want to count those that appear on the breakfast, lunch and dinner differently. how can i do that?
    thanks

    1. Hi Abraham,

      If you can provide more details about your data structure, we will try to make a formula.

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