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

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

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

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

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

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

  6. Hi Alexander

    Have send the Sample Workbook for your ready ref.

    Kind regards

    Rishi Bansal

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  39. Svetlana Cheusheva,hope yo helping me,thanks

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

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

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

  43. hi,
    i have got a big data of employees working for a mining comapany, i want to know the number of employees that come for breakfast, lunch and dinner differently. how can i do that?
    thanks

  44. Hello. I’m trying to count unique text values in column A that meet date criteria in column G as determined by one cell (H158160) that contains a drop down list of dates selected by users. The intention is for them to be able to select the date from the list and see the count of account numbers, the sum of units sold, and the sum of revenue. I’ve got all of the formulas working except for the count of account numbers, because I’m having trouble with the COUNTIFS formula. I’ve been able to count unique values using the array formula {=SUM(1/COUNTIF(A2:A796,A2:A796)}, but I don’t know how to set these multiple criteria. Hopefully you can help. Thanks in advance!

  45. I am trying to count the number of people who have done an intervention by the place from which they come from. I am taking this information from another sheet. The thing is they have done up to four interventions and the intervention columns are not in direct order of one another. So I am trying to count the number of people say playing football which could be in four columns by the place the live.

    I have tried =COUNTIFS('USER SHEET'!AC2:AC10000,Intervention!D3,'USER SHEET'!B2:B10000,Intervention!C4,('USER SHEET'!AF2:AF10000,Intervention!D3,'USER SHEET'!B2:B10000,Intervention!C4,('USER SHEET'!AI2:AI10000,Intervention!D3,'USER SHEET'!B2:B10000,Intervention!C4,('USER SHEET'!AL2:AL10000,Intervention!D3,'USER SHEET'!B2:B10000,Intervention!C4))))

  46. How chose two fromm three number the best.
    Excample:
    A: 30
    B: 20
    C: 10
    Two the best 30+20=50

  47. I am trying to use a count function, but struggling to get the formula to work.

    I have an area that will be filled in with different dates. On a separate sheet I have a running date column. I want to count how many boxes have a certain date in it.

    =COUNTIF(Sheet3!$C$23:$EF$34,"=14/8")

    This is the formula I'm currently using, which works fine, but I cant drag it down. I've tried doing the alternative:

    =COUNTIF(Sheet3!$C$23:$EF$34,"=B8")

    (B8 is where the 14th August is located). This does not work.

    Please help.

    Thanks
    Craig

    1. JUST REMOVE THE $ SIGN AND DRAG DOWN

  48. Good Morning,

    I am so thankful I found this site.
    I am looking for a simple formula to find all 4s in column b, that has a code of X3,Z1, and Y9 in column E, PLUS all 5s in column B, that have blanks in column D, with codes of X3, Z1 and Y9 in column E.
    Can this be done? If not, thats fine, I will complete it the same as always.
    I appreciate all your help.

    Rick

    A B C D E

    5659 4 91B 91B2 Z1
    2935 4 68C 68C2 Z1
    6572 4 92W 92W2 Z1
    8675 4 91D 91D2 Z1
    7627 5 12B 12B2 Z1
    7450 5 19D 19D2 X3
    9171 5 36B 36B2 Z1
    3503 5 11B 11B2 Z1
    5759 5 36B 36B2 X3
    5062 5 15D 15D2 Z1
    9088 4 91E Y9
    1665 4 68W 68W2 x1
    7212 4 68W 68W2 x1
    4539 4 15U 15U2 Z1
    2579 4 74D 74D2 Z1
    8052 5 14H X3
    2423 5 15U X3
    5228 5 12P Z1
    9080 5 19D Z1
    9076 5 11B x1
    691 5 74D x1
    3504 5 31D x1
    8594 5 14E x1
    919 5 11B x1
    4518 5 11B X3

  49. Hi!

    I have two columns (all text). The first column is a list of names (could be repeated) and the second column is a list of factors (i.e. "ontime", "late", etc.). I want to know how many times a certain name (from the first column) hits each factor from the second column. Hopefully this is clear.

    Thank you!

    1. Hi Again,

      Please disregard. I used a SUMPRODUCT formula which worked like a charm!

      1. Hi Melissa. How did you get this to work?

  50. Hi I am trying to use a countifs formula for the following situation:
    I have multiple staff and I want to get a tally of how many times each staff puts in overtime for each reason for overtime.
    I have been successful with this but I want to take it to the next level and find out the total amount of time corresponding to each reason for each staff member. So the formula is referring to a separate tab in excel and basically saying that if B4:B30 says "X" and c4:c30 says "Y" I want the value of D4:D30 that corresponds to this condition to show in the cell. Is this possible?

    1. Hi Kim,

      Let me check if my understanding of the task is correct. so, if B4 says "x" and C4 says "y", you want to pull the value from D4. If so, you can enter the following formula in row 4 and then copy it down to other cells in the column:

      =IF(AND(B4="x", C4="y"), D4, "")

      If you want to sum the numbers in D4:D30 that correspond to those conditions, then you can use the following SUMIFS formula:

      =SUMIFS(D4:D30, B4:B30, "x", C4:C30, "y")

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