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 3. Total comments: 599

  1. Hi,

    I'm trying to create a spreadsheet for work which will calculate the statistics for each month throughout the year based on another spreadsheet we use to hold all the data.

    So we have a column with received date, a column with District and another column with an outcome.

    What I'm trying to do with limited success is Check "Date Received" column for any dates within each month, then check another column with those dates which matches 1 of 5 words added to that column.

    Example 1:

    Column A - Check for all Cells with Dates between and including 01/01/2023 to 31/01/2023.
    Column C - Check for all Cells with Text containing exactly "POSITIVE".

    Below my Formula (I have a separate datasheet hidden on the workbook which contains all my month dates).

    =COUNTIFS('[SS Stats 2023.xlsx]2023'!$I:$I,"Positive",'[SS Stats 2023.xlsx]2023'!$A:$A,">="&Data!A1,'[SS Stats 2023.xlsx]2023'!$A:$A,"="&Data!A1,'[SS Stats 2023.xlsx]2023'!$A:$A,"="&Data!A23,'[SS Stats 2023.xlsx]2023'!$A:$A,"="&Data!A1,'[SS Stats 2023.xlsx]2023'!$A:$A,"<="&Data!A2)

    Any assistance on this would be great, as its driving me up the wall right now, it should work and I cant see why it doesn't :(

    1. Welp same issue again.

      =COUNTIFS('[SS Stats 2023.xlsx]2023'!$I:$I,"Positive",'[SS Stats 2023.xlsx]2023'!$A:$A,">="&Data!A1,'[SS Stats 2023.xlsx]2023'!$A:$A,"="&Data!A1,'[SS Stats 2023.xlsx]2023'!$A:$A,"="&Data!A23,'[SS Stats 2023.xlsx]2023'!$A:$A,"="&Data!A1,'[SS Stats 2023.xlsx]2023'!$A:$A,"<="&Data!A2)

      1. Ok I give up, it wont let me post my message or any variation of it, and all it does is make it look like my formula is massively wrong when the above formula has some "=" statements inbetween the ">=" and "<=" statements. I dont know how to fix it.

  2. Hi
    I am trying to write a count if formula to count between two dollar figures...which appear

    C4-DB4 is a run of varying $ amounts
    DE4 is the max value (FORMULA)
    DD4 is 10% less than max value (FORMULA)

    =COUNTIFS(C4:DB4,">=DE4", C4:DB4,"<=DD4")

    The above forumla wont count the number of $ values between max and 10% discount. Please help!

  3. Hi,

    Is it possible to tag a progress with each cell containing different data?

    The information will be tagged as "complete" if either "email", "mobile no.", or "address" is provided. It does not necessarily need to have all the information (at least one of the 3 should be fine)

    Thank you!

    Steve

  4. Hi Alex... Im suffering here...

    I need to count how many F there are in multiple colums

    If I count the "F" in one column, works great:
    =COUNTIFS(A:A,"JUNE",B:B,"F")

    but what i really need is to count the "F" in multiple columns:
    =COUNTIFS(A:A;"JUNE",B:K,"F")

    and it doesnt work, what am I doing wrong? is there a way I can do this?

    any help, will be appreciated.

    thank you! claudia

    1. Hello! All the ranges you use in the COUNTIFS function must be the same size. That's why your formula doesn't work. I recommend reading this article: Excel COUNTIF and COUNTIFS with OR logic.
      Here's an example of the formula:

      =COUNTIFS(A:A,"JUNE",B:B,"F") + COUNTIFS(A:A,"JUNE",C:C,"F") + COUNTIFS(A:A,"JUNE",D:D,"F")

      1. oh... got it..that is what i was afraid of... i didnt want to make the formula too long...
        thank you so much, let me check that part of the article again!

        thank you, Alex!!

  5. Hi alexander,
    I have two column of products and I need to find out if a certain brand of a retailer is offered at the other retailer in the other column. Offered by 'retailer ' 'yes' = 1, if not offered then = 0
    is it possible to find out if a brand is offered in the other column without using a specific wildcard? otherwise the wildcard will have to be changed per product and this would not be possible.

  6. Hi, I got small problem.

    I cannot find a way to formulate a suitable formula. I hope you can help me.

    I have to count how many of the people who worked on the DON project were between the 16 and 24 years, male, from China. I need to include all of this as of 12/31/2015. Most important is fact that some of people quit work before that date so I need to exclude them.
    Start of contract is between C3 to C100, end of contract D3 to D100, Birth date E3 to E100, country of residence is F3 to F100, project name is G3 to G100, gender I3 to I100.

    I made it as per the formula below, but I don't know how to count the age bracket correctly (between 16 and 24 years) and eliminate people who finished work before this date (12/31/2015).
    =COUNTIFS(C3:C100, "<=12/31/2015", F3:F100, "China", G3:G100, "DON", I3:I100, "M", )

    TIA John

    1. Hello!
      The COUNTIFS function cannot use other functions or formulas as arguments. Therefore, I recommend using the SUMPRODUCT function.

      =SUMPRODUCT(--(C3:C100<=DATE(2015,12,31)), --(F3:F100="China"), --(G3:G100="DON"),--(I3:I100="M"), --(DATEDIF(E3:E100,TODAY(),"y")>=16), --(DATEDIF(E3:E100,TODAY(),"y")<=24))

      You can also find useful information in this article: How to calculate age in Excel.

  7. Hi Alexander,
    Im a new user, how do I express eg that the Yes must relate to the occurrences of LS rather than any Yes that appears in the column.
    =COUNTIF('Action Log'!G23:G83,"LS")-COUNTIFS('Action Log'!I23:I83,"Yes")
    TIA Karen

    1. Hi! If both of your conditions must be met simultaneously, use the COUNTIFS function to calculate the quantity of values. Read this article above.
      Try this formula:

      =COUNTIFS('Action Log'!G23:G83,"LS",'Action Log'!I23:I83,"Yes")

  8. I am trying to incorporate wildcards into a COUNTIFS with three criteria because the data is coming from a sharepoint and sometimes includes date and time and sometimes only date and I need to look only at dates. So far this is what I have.

    =COUNTIFS('WT Outflow Import'!B:B,$B$4,'WT Outflow Import'!A:A,C18,'WT Outflow Import'!C:C,$B$6)

    B6 is the cell containing the date.

    The above formula returns what I need if there is no time present with the date in the cell. Otherwise it returns nothing as expected. I need the value matches regardless of whether there is date/time or just date. This is a sharepoint connection, so I don't have the luxury of just changing the data.

    I tried using a wildcard and it also returns nothing.

    =COUNTIFS('WT Outflow Import'!B:B,$B$4,'WT Outflow Import'!A:A,C18,'WT Outflow Import'!C:C,$B$6&"*")

    Any idea where I am going wrong here?

    2. Is it because the column where I am parsing using the wildcard is a date (number)?

  9. Hi there, I am trying to program a workbook to pull data from different sheets and am hitting a roadblock in trying to program my COUNTIFS formula with multiple criteria.
    I am pulling survey data and am looking at the number of compliments per city. In this instance, I have it programmed to pull the comment number that are compliments and from the city, but where I am running into trouble is that the city field comes back to me and can reflect the city of North Fort Myers as "NORTH FORT M" and "N FORT MYERS".
    I am trying to figure out how I can program it so both of these are picked up for the same total number. I currently have my formula as =COUNTIFS(Test!O:O,List!W2,Test!D:D,List!C2). But for the data in column D, I want it to be pulled if it reflects the info from cells C2 and D2.
    Hoping this is clear and you can help! Thanks.

  10. Hi Alexander,

    I'm trying to use Countifs with Text.

    I have a column with Girls attending classes. In that column I write yes or No.

    I then would like to calculate or create a formula in a separate column, that if that specific cell says "Yes" it would allow the formula to occur if it says "No" it would just appear as £0.

    Hoping you can help me.

    Thanks

    2. Is this also possible to occur with a Dropdown with either yes or no? And How would I allow the formula to occur or have it cancel it out if it say no.

      Thanks

  11. Hi Alexander,

    What would be the formula for the following example:

    Column A has three entries "house", "bungalow" or "flat". Column B has the 'area' of the building. I want to calculate the total area when Column A has either "house" or "bungalow" entered.

    I hope this makes sense!
    Thanks :)

  12. Hi, I am trying to total how many times a participant has attended a class over a 6 week period. I have Column A containing the names of participants, column B containing the date attended. These participants names listed in column A are repeated in alot of cases as they might attend multiple times over this period. I am wanting to lookup column A, find the exact same names/search criteria and return a total "number" for the sum of how many times their name appears over this period. This would then tell me that "Joe Blow" attended this class 20 times over a 6 week period. Thank you. Ruth :-)

    1. Hi! I hope you have studied the recommendations in the tutorial above. It contains answers to your question. Here is an example formula for your case:

      =COUNTIFS(A1:A100,"Joe Blow", B1:B100,"<"&TODAY(), B1:B100,">"&(TODAY()-42))

  13. Hi,

    I have 2 columns. Column A shows the time and Column B indicates if a button is pushed with 1='button pushed' and 0='button not pushed'. i need to measure the time between every button push (or the number of cells between each '1' because each cell is equal to 1 minute) but the data spans over 2 weeks (=20 000++ cells). is there a way to make a list of the duration for every "downtime" (time between every button push) without doing them one by one?

    for example, collumn B would look something like this:
    0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,....
    so the first downtime is 22 minutes, second downtime is 6 minutes, third downtime is 18 minutes and so on.

    thanks in advance!

    1. Hi! If I understand your task correctly, the following formula should work for you:

      =SEARCH("#",SUBSTITUTE(CONCAT($B$1:$B$3000),"1","#",ROW(A2))) - SEARCH("#",SUBSTITUTE(CONCAT($B$1:$B$3000),"1","#",ROW(A1)))-1

      Copy this formula down along the column. I hope this will help.

      1. Thank you so much! it works wonderfully!

        I changed the '3000' in '$B$3000' to the number of the final cell.

        why do we need to use ROW(A1)? I substituted it with 1,2,3,.... and it work just fine, but your method also work. what is the difference?

        1. If you copy the formula down along the column, the value of ROW(A1) will automatically change and return 1,2,3...

          1. Ok everything is clear now! thanks again!

  14. I need help in my file as per below sample.
    I have multiple items, some are with same "PCA P/N" and some are not same and it has different date reject each PCA P/N recorded.
    Then, what I need is to count the specific PCA P/N which still "OPEN" status and with less than 30days from now.
    Another column and formula is for less than 60 days, and next is less than 90days from now.
    For example the Date Reject is at column A:A, PCA P/N is at column B:B and the status is at column C:C.

    Date Reject PCA P/N STATUS
    5-Aug-22 AEM5300-66402 open

    Hope you guys understand my statement.

      1. Thanks for your response. But after I've tried, this is the results.
        for this one -> =SUM(((TODAY()-A1:A20)<30)*(B1:B20="AEM5300-66402")*(C1:C20="open")) results shows "0".
        and for the 2nd formula shows #VALUE!.
        I'll check on your link provided for more other info.
        Thanks again.

        1. I'm checking the error and evaluate the formula shows the date is "45072-A1:A20".What do i need to change or format?

  15. Hi, Please help with the below formula.

    The top performer's indication with a yes or no is in column "L". Country is in column X. Now I would like to understand the % of top performers in the United Kingdom and Ireland countries. Used the below formulae but couldn't get the correct %. Please help.

    =COUNTIFS('Successors List'!L:L,"Yes",'Successors List'!X:X,"United Kingdom","Ireland")/COUNTIF('Successors List'!L:L,"Yes")

    1. * To clarify, the "Successors list" is the top performers list in the Excel file.

  16. Hi!

    Would love some formula help here, if possible.

    Sheet 1: Customer email (column A), Survey completion date (column B)
    Sheet 2: Customer email (column A), Order date (column B), Unique order # (column C)

    On Sheet 1 in column C, would like a formula to count quantity of unique orders on Sheet 2 by customer email BEFORE OR ON the survey completion date

    Is this something you could help me with?

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =SUM((COUNTIFS('1'!A1:A10,'2'!A1:A10,'1'!B1:B10,">="&'2'!B1:B10)))

  17. Hi Alexander,
    I follow your topics and website. You are genius of solving the problems. And you help me many times here.
    My question is:
    I have one cell A1. Every week in this cell is entering one of this letters ("A", "B", "C","D") from another sheet. . I want to count this letter and multiply by different number.
    For Example: countif(A1,"A")*13.75 ........... countif(A1,"D")*2.75 ............ countif(A1,"C")*5.5 ............... countif(A1,"B")*8.25
    I want to put this in one formula.
    Thank you

    1. Hi! You can get a number depending on the value of the cell using the IFS function.
      IFS(A1="A",13.75,A1="B",2.75,A1="C",5.5,A1="D",8.25)
      But I'm not understood what result you want to get and why you should use COUNTIF in the one cell.

      1. I thought that I have to use countifs, because i have multiple criterias. Also think about IF with OR. But couldn't put together.
        Thank you for quick response.
        Result is based on £. For example: if in that cell A1 is D, then I want to be multiplied by 2.75£. People are receiving bonuses for their hard work.

        1. I make setup with your formula IFS and this solve my problem.
          Thank you very much.

  18. how can i count total number of unstanding payment for one registration number ( different pay rate) from different worksheet( one worksheet for one month) for the whole year.

  19. Hi,

    How do copy the formula to another cell with skipping AZ-BA (2 Columns) of data.

    =COUNTIFS(AV$6:AV$75,"12x12",AW$6:AW$75,"Prep")+COUNTIFS(AV$6:AV$75,"12x12",AX$6:AX$75,"Prep-c")+COUNTIFS(AV$6:AV$75,"12x12",AY$6:AY$75,"Prep")

    into

    =COUNTIFS(BB$6:BB$75,"12x12",BC$6:BC$75,"prep")+COUNTIFS(BB$6:BB$75,"12x12",BD$6:BD$75,"Prep")+COUNTIFS(BB$6:BB$75,"12x12",BE$6:BE$75,"prep")

  20. Hi! I would like to ask about, what formula is the best for this situation:-

    COLOUMN A : Name of officer
    COLOUMN B : Name of officer
    COLOUMN C : Numbers of day taken to complete the task

    Reason why there are two column is the task need to 2 officers to be completed. So the question is, how do I want to count if the officer (e.g Lisa) has completed her task less than 7 days.

    Thank you.

  21. I need some help! I am an OB provider and in the last 6 years, my male-to-female fetus ratio is 2:1 (which is wild). I am trying to see if the sex discrepancy is the strongest based on specific indications (such as mothers over 35 or patients I saw due to sonogram anomalies). I have my indications (the reason they saw me) in one column and male or female in another column. I use this [COUNTIF(F:F, "(u/s)*")] to indicate patients that were seen due to sonogram anomalies and [COUNTIF(I:I,"*male*")] to indicate all male fetuses.

    Can I combine the two and see whether the indication changes the sex ratio?

    1. Thanks for your answer!
      Maybe I didn't understand something because when I try to apply the formula to the desired column it doesn't show the correct results. For example I know for sure at least 5 cells contain simultaneously two certain words, and still the formula tells me only one cell does, or none.

      I mean, does it count how many cells IN TOTAL in a certain column contain at least the words used in the formula, or the formula just works for each cell individually? Sorry forr my question, I'm not that familiar with Excel.

      Thanks again,

  23. Hi,
    I wonder if you could help me. I have table need to find the correct formula to use,
    i want to count the duplicate with multiple creteria.

    example :

    time date customer Flavour number invoice
    2:43 am 07/04/2023 linda jitsu 123
    2:44 am 07/04/2023 linda Belt 456
    2:45 am 07/04/2023 Henry Belt 789
    2:45 am 07/04/2023 Henry Belt 897
    2:45 am 07/04/2023 Henry jitsu 456
    2:46 am 07/04/2023 Henry Belt 789

    how we use countif for flavour Jitsu will show duplicate if more than 1 time entry and for flovour Belt will show duplicate if more than 3 times entry?

    Hoping for your help in this matter .

    Thank you very much

    Best Regards

  24. I'm working on a spreadsheet where the COUNTIF formula was pulling from a local desktop so the formula is now giving me a "REF" error since that employee is no longer here. I can see where I can change the path of the formula but unsure what Table2[#Data],"Administration" is coming from. As I don't see any tables to reference on that report when generated.

    =COUNTIF('C:\Users\employee\Downloads\NewHireReport (20).xlsx'!Table2[#Data], "Administration")

  25. Hi,

    I have a formula =COUNT(IF(MONTH($M:$M)=4,1)) which returns how many dates in a column are in each month (April in the example). However I want to combine this with a search in another column "=Yes". But when I am using COUNTIFS I am getting an error. So in essence I want to calculate how many lines are 'Month' and 'Yes' (where the month is currently dd/mm/yyyy). Any advice greatly received

    1. Hello!
      The COUNTIFS function cannot have only a cell value as an argument, but not a formula. Therefore, use the SUMPRODUCT function to calculate values with calculations.
      If I understand your task correctly, try the following formula:

      =SUMPRODUCT(--(MONTH(M1:M10)=4),--(N1:N10="yes"))

      1. Thank you, however I am coming up with #value!. I believe this maybe because not all the values in column M are dates. The column is linked to arrival dates and if the goods have arrived the cell states 'here'. Can I adapt the formula above to state sumproduct IF cell is date (month = 4) and column N =yes?

          1. Thank you, worked perfectly :-)

  26. I need to be able to check if the cell is blank. If it's not, then check the cell for specific text. If that cell is blank, I need to check a separate cell for the same text. Any suggestions on how to do that?

    Example in words, if cell A is not blank, check cell A for "apples", if A is blank, go to cell C and check for "apples".

    2. I forgot to say, I'm checking a cell range: i.e. A2:A50 or C2:C50

  27. Hi, looking to use countifs to help with a roster I'm working on. Column C = days of the week, I want the formula to count all cells containing the initials 'RT' in columns F-J if the corresponding row in column C = Friday. Have read the article above, and am trying this: =COUNTIFS($C$4:$C$49,"Friday", $F$4:J$49,"RT") but getting a #VALUE error. Would really appreciate any help, I'm not very good on Excel

    1. Hello,

      I need help developing a formula.

      I have one column with names such as Sarah, William etc between G2:G100. And a seperate column with multiple options such as completed, pending, not completed between I2:I100.

      I want to count specifically for an employee (Sarah G2:G100) who has the following answers in I2:I100 "completed" or "pending" or "blank cell" but not count the other answers.

      Tried many different formulas. Can you help?

    2. Hello!
      The COUNTIFS function can only use ranges of the same size. Your data ranges have different dimensions. So use the SUM function to calculate cells where both conditions are met.
      Try this formula:

      =SUM(($C$4:$C$49="Friday")*($F$4:J$49= "RT"))

      1. That worked, thankyou soooo much :-)

  28. Hi There,

    I'm looking to find a formula that takes Column A (Date) from sheet 2022-2023 + anything in Column H that contains a word (could have other words in it as well) from sheet 2022-2023 and counts it in a cell on another sheet. so basically Column A equals a certain date and if it meets that criteria, count the number of cells in column H that contains that certain word (plus could have other words in the cell)

    The closest I have to this is =SUMPRODUCT(--('2022-2023'!A:A=DATE(2023, 1,31)),--('2022-2023'!H:H="Leaders")) but its only counting the exact text where some other cells have "Leaders, Supervisors" and it won't include it.

    I've tried this too but getting an Error =COUNTIFS('2022-2023'!A:A,DATE(2023, 1,31),('2022-2023'!H:H,"Leaders"))

    Thanks for the help!

    1. Hello!
      You can determine a partial match of a word and a text string with the combination ISNUMBER + SEARCH functions.

      =ISNUMBER(SEARCH("Leaders",H1:H10))

      I hope this will help, otherwise don't hesitate to ask.

      1. Hi there,

        It didn't quite work as I'm not looking for a true/false response but more so how many cells contain that word. so for example if there are ten cells in the A column that have the same date, how many of those ten cells in the H column contain the word leaders

        1. Hi!
          This works great if you apply it to your SUMPRODUCT formula.
          For example,

          =SUMPRODUCT(--('2022-2023'!A:A=DATE(2023, 1,31)),--(ISNUMBER(SEARCH("Leaders",'2022-2023'!H:H))))

          I can't check the formula that contains unique references to your workbook worksheets.

  29. Hello, the following formula counts the number of times names from a range of cells get listed.

    =SUMPRODUCT(8*ISNUMBER(SEARCH(B51,$C$18:$P$41)))

    Is there a way to ad a seperate individual cell to the formula to be counted along with the existing range? The individual cell is N4

    1. You are awesome....I just had to change N4 to $N$4 and it worked great.

      =SUMPRODUCT(8*ISNUMBER(SEARCH(B51,$C$18:$P$41)))+(1*ISNUMBER(SEARCH(B51,$N$4)))

      Thank You so much!

    2. Hi!
      If I understand your task correctly, try the following formula:

      =SUMPRODUCT(8*ISNUMBER(SEARCH(B51,$C$18:$P$41)))+(1*ISNUMBER(SEARCH(B51,N4)))

  30. Hi... I have a data where in one column there is a request I'd and on the other column there is a description of activity, e.g. updation of Address, Name, Swift, Postal etc. I want to count the occurance of unique activity with reference to request I'd. Though if there are 4 similar request I'd and activity description against it is updation of Address for 4 times in a row but I have to count them as 1 if all 4 has criteria address into it. So even if the count is 4. I want a formula to count it as 1 only. That means the order entry person worked on the same request 4 times will be considered as 1 request, but if there are other activity against the same req id, it should also show 1 count for it as well. but if the occurance of another activity is more than one it should restrict the count as 1 only.
    Kindly help with your expertise

  31. Hi!

    What if I need to know if a certain number is between 10% above or below another?

    Example:

    Seller A price: 100 | Seller B price: 110 = Seller A's price inside of 10% of seller B's price

    Is this possible with countif?

  32. Hello!
    Give me an example of the data of how several names are written in a cell, and what separators are used between them. Then I'll try to give some advice.

    1. The data is just names separated by a forward slash as the delimiter. Ex. John Doe / Tim Hobbs in one cell, and Jeffrey Beckham in another.
      Currently John Doe & Tim Hobbs aren't being counted,

        1. This did something totally unexpected to the totals inside my table where all the name counts were the same. Almost like it added them all together. I wanted them to be counted individually, even if they are in the same cell.

  33. Hi i have a report that has 8 sheets(by Names) which has a table of contents that i need to count based individual text. However with the countif formula, it came out as date (I.E. 1/0/1900)
    Formula as follows
    =COUNTIFS(Joel!F4:F25,"NICF019")+COUNTIF(Ivan!F4:F25,"NICF019")+COUNTIF(Amanda!F4:F25,"NICF019")+COUNTIF('Wai Loon'!F4:F25,"NICF019")+COUNTIF('Zu Yang'!F4:F25,"NICF019")+COUNTIF(Elly!F2:F25,"NICF019")+COUNTIF(Hazel!F4:F25,"NICF019")+COUNTIF(Kenneth!F4:F25,"NICF019")

    Is the formula incorrect?

      1. This works well. My formula is okay. Have change the cell to general. :D
        Million Thanks

  34. Hi!
    The checkbox returns the value TRUE or FALSE in cell G2. Read more about it here: How to insert checkbox in Excel. Use the value of cell H2 in the COUNTIF formula, in which the formula will be written, for example =IF(G2=TRUE,DATE(2023,1,1),"")
    I hope it’ll be helpful.

    1. Thanks for your reply.

      May I ask,

      What if I have two checkboxes in the same cell, one option being AM and another being PM. How can I tally up the number of checked boxes for each option? Eg, In G2 the AM checkbox is checked and PM is unchecked, whilst in G3 the PM box is checked and AM is unchecked ... meaning the totals are AM 1 and PM 1. Which formula can I use to show these totals?

      Really appreciate any help.

      Thank you

      1. Hi!
        Two checkboxes in the same cell cannot have different values. What you're talking about are checkboxes in two different cells.

  35. I'm struggling to make my countifs work.

    I'm using the following formula, and it's giving a zero result when I know it should be a 1
    =COUNTIFS('January 2023'!A10:A100,"C38",'January 2023'!C10:C100,"Yes")

    "C38" is what I want it to look up (a name) and January 2023 is the sheet with the raw date that needs to be displayed.
    I'm trying to find out how many times a particular name has been marked "yes" in January.

    is there something I'm missing?

    Thanks

    1. Hi!
      If you want to find the value in cell C38, don't use quotation marks.

      =COUNTIFS('January 2023'!A10:A100,C38,'January 2023'!C10:C100,"Yes")

      I hope it’ll be helpful.

      1. It worked!

        Thank you so much for your help!

  36. I hope you can help. I work on chevy cars. occasionally i work on a non chevy car. I am trying to count the number of cars I have worked on in a column that do not match the chevy cars listed in another column. I have this formula but it is counting the blank rows of cars i have yet to work on. =SUMPRODUCT(--(ISNA(MATCH(E3:E200,N3:N20,0))))
    This is the formula i am using for counting cars that are chevys. =COUNTIF(E:E,N19) (N19 being a Tahoe)
    Trying to change that to not equals () an array (N3:N20) just gets me an error.

    Thanks for your help.

    1. Hello!
      If I understand your task correctly, try the following formula:

      =SUM(--((IFNA(MATCH(E1:E2000,N3:N20,0)+1,1)*(--NOT(ISBLANK(E1:E2000))))=1))

      1. Thanks for your help, that works great!

  37. Good morning,
    apologies if already covered and missed but i would greatly appreciate your help to formulate the below:

    Count/sum if:
    Column A = X
    Column B = Y
    Between dates 01/01/2023 & 31/01/2023

    I can get the first two, getting the count/sum of the cells that meet the two conditions but struggling to get the date range factored in as well

    2. Apologies, the variation on this I am struggling with is also:

      Count/sum if:
      Column A = X
      Column B = Y
      if Column C = a January date

  38. Hi, I would like to ask for advice. I am currently working on a database. Is there any formula that can help me count the number of entries in a range of cell?

    Example:
    Employee X has scheduled leave dates 10, 15, 23 for January and 5, 9 for February and so on for the whole year(considering each month are separate columns)
    I would like to count the total number of days Employee X have for the whole year.

    Thank you so much in advance!

    1. Hello!
      If the column contains the number of vacation days, use the SUMIFS function to calculate the sum of days for an individual employee. If each vacation day is specified separately, count the number of days using the COUNTIFS function.
      I hope I answered your question. If something is still unclear, please feel free to ask.

  39. Hello
    Can you please advise on how to use the COUNTIFS if I would like to calculate occurence of an individual on a rota on weekdays and weekends.

    Thank you,

  40. hi,
    what is wrong with this syntax ; if the question is ... The choices if "Teacher" or "Student" and under the category "No. Willing to Pay More (PHP 36 - 45 and PHP 46 - 55)"
    =COUNTIFS(E5:F19,"Teacher",P5:P19,"ü") note: "ü" character code in excel represents the check mark (✓) character in the column of P5:P19
    it will just display an error of "VALUE!
    while using...=COUNTIF(E5:F19,"Teacher") and =COUNTIF(P5:P19,"ü") individually has no problem at all..
    Thanks so much..Appreciate so much for your immediate response.

  41. Hello I hope you can help. I've had a read through your tutorial and a few other pages. I'm new to excel but I'm currently building an email tracker for data from Outlook. We use tags on the emails that we can count/filter by. So I can count the amount of times an agents name shows up in total, we also use tags to highlight email types eg New Task, Chase ect. The problem I'm having is that the raw data puts all these tags in the same column in excel separated by a comma.

    I have these in a table with agent name in Column A and the email job type in Row 1. If there a way for me to count the amount of times an agent name for example John Smith shows up in the same cell as an email job type for example New Task? The aim is to see how many of each type of email each agent deals with.

    1. When I say "I have these in a table with agent name in Column A and the email job type in Row 1" I mean I am trying to pull the data into a table set out this way.

  42. Hello, I tried to count all rows containing both, lets say, "Apples" in column A and "Sold" in column B. This works fine, but when I try and count rows containing both, lets say, "Apples" in column A and EITHER "Sold" in column B OR "Sent" in column C, I don't know how to do it. I tried the below, but it double counts if a row fits both the column B and C criteria:

    =COUNTIFS(A1:A1000, "Apples", B1:B1000, "Sold") + COUNTIFS(A1:A1000, "Apples", C1:C10, "Sent")

    Is there a way to do this without double counting?

  43. hi im looking for a formula on to automatically count a data if a condition is met....for example
    column1(item number) column 2 Column3
    1 store1 Pencil
    2 store2 Ballpen
    3 store2 Paper
    4 store1 Eraser
    5 Store2 Notebook
    6 Store2 paper Clips
    7 store1 Calculator

    what i want to do is i can automate it into like this
    Result1 Store1 Item no. 1, 4, and 7
    Result2 Store2 item no. 2-3 and 5 - 6

    thanks in advance

      1. big thanks

  44. Hi, thanks for all the examples. I have a particular one that I haven't been able to figure out.
    I need to get a count of particular items that were purchased after a specific date. My example is if I have Apples, Oranges, and pears on a list and I have purchased several of these every day from Sept 1st, 2022 to Oct 31st, 2022. I want to count just pears purchased after Oct 1st, 2022. I have every purchase item listed with it's own date. I just need excel to sort this for me on an excel cell. I can do this through pivot table but this won't help me in this example.
    Thank you

  45. hi, how can i make a sum and countifs with mulitple criteria in cell link instead of text? for example: sum(countifs(A3:A7,{"cancelled","apple"})) now instead of using cancelled and apple, i will use cell link like A17 FOR CANCELLED AND A18 FOR APPLE. Is this possible?

    Appreciate your help! many thanks

  46. Hi! I hope you can help me I keep getting errors when I try to build this problem into IF and adding multiple => than x.

    I am trying to build a formula where I want a blank cell come back with (High, Medium, Low) when there is a specific number in another cell. For instance some types a number between these ranges for High (12 to 13 ), Medium (7 to 11), low (4 to 6)

    Hope this makes sense, please advice and your recommendation if any, thank you

  47. I have spent hours trying to make this work. I went through your entire tutorial (which is awesome by the way!) but cannot seem to tweak your formulas to fit my needs where it actually works.

    Column M contains dates. I want to find the dates between 9/1/20 and 9/30/20.
    Column C contains text. I want to find any matches to the word deviation.
    Column E contains text. I want to find all occurrences except cancelled.
    I want to count all the instances of occurrences between that date, that match the word deviation, that aren’t cancelled.

    I have tried all of these and none work. Can you please help me?
    =COUNTIFS(Metrics!M2:M1829,">9/1/2020",Metrics!M2:M1829,"<9/30/2020",Metrics!C2:C1829,"*Deviation*",Metrics!E2:E1829,""&Cancelled)
    =COUNTIFS(AND(Metrics!M2:M1829,">9/1/2020",Metrics!M2:M1829,"<9/30/2020"),(Metrics!C2:C1829,"*Deviation*"),(Metrics!E2:E1829,"*Cancelled*"))
    =COUNTIFS(Metrics!M2:M1829,">9/1/2020",Metrics!M2:M1829,"<9/30/2020"),(Metrics!C2:C1829,"*Deviation*"),(Metrics!E2:E1829,"*Cancelled*")
    =SUM(COUNTIFS(Metrics!M2:M1829,">9/1/2020",Metrics!M2:M1829,"<9/30/2020",Metrics!C2:C1829,"Deviation",Metrics!E2:E1829,"Cancelled")
    =SUM(COUNTIFS(Metrics!M2:M1829,">9/1/2020",Metrics!M2:M1829,"<9/30/2020",Metrics!C2:C1829,"Deviation",Metrics!E2:E1829,"Cancelled")

    1. Hello!
      If I understand the question correctly, try using the "<>" sign in the condition.

      =COUNTIFS(AND(Metrics!M2:M1829,">9/1/2020",Metrics!M2:M1829,"<9/30/2020"), (Metrics!C2:C1829,"<>"&"*Deviation*"), (Metrics!E2:E1829,"<>"&"*Cancelled*"))

      1. Unfortunately that doesn't seem to work. The old "There's a problem with this formula" box comes up. It's highlighted all of the parenthesis, if that's helpful at all.

        1. Hi!
          I can't check the formula that contains unique references to your workbook worksheets. Try removing the parentheses from the formula.

          =COUNTIFS(Metrics!M2:M1829,">9/1/2020", Metrics!M2:M1829,"<9/30/2020", Metrics!C2:C1829,"<>"&"*Deviation*", Metrics!E2:E1829,"<>"&"*Cancelled*")

  48. Hi,

    I'm trying to create a QA scorecard for my team. I hope someone can help me.

    What I'm trying to do is that create an point system with my scorecard. Let's say that the passing QA score is 80, that would be equivalent to 30 points. Every time the QA score is deducted with 3, the points will be deducted with 2.

    Example:

    80 = 30 points
    77 = 28 points
    73 = 26 points
    70 = 24 points
    67 = 21 points

    and so on.

    1. Hello!
      Divide the reduction size by 3 and take the integer part of the number using the INT function. Multiply by 2 and get points.

      =INT((80-A1)/3)*2

      Note. 67 = 21 points - incorrect.

    2. I'm sorry, there's a mistake. It's supposed to be:

      80 = 30 points
      77 = 28 points
      74 = 26 points
      71 = 24 points
      68 = 22 points

  49. Im trying to find a formula for the following situation.
    I hope I ill be able to explain it clearly.

    1 basket contains 3 apples, 4 bananas, 7 oranges

    I would like to be able to change the basket number for example to 2 and I would like excel to calculate how many fruits I need for 2 baskets. And when I change it for example to 10 baskets it needs to tell me how many fruits I need to fill each basket.

    Thank you for your help

  50. I have a spread sheet that I copy data into from a different spread sheet that tracks quality defects of our products by Date, Model, Serial Number and Production Line. We have some products that have multiple quality issues tied to one serial number that have to be fixed and some that only have one issue.

    What I am trying to do is get the count of quality defects by date, but not to include duplicate serial number entries. The formulas below get me different parts of the issues I'm trying to track.

    The first one will get me the number of units that had defects that particular day, but it won't exclude the duplicate serial entries.

    =COUNTIFS(X2:X1800,"="&DATE(2022,8,22))

    The second formula will get me the number of non repeating serial numbers but isn't able to be constrained to a certain date.

    =SUM(IF(FREQUENCY(IF(LEN(AE2:AE1800)>0,MATCH(AE2:AE1800,AE2:AE1800,0),""),IF(LEN(AE2:AE1800)>0,MATCH(AE2:AE1800,AE2:AE1800,0),""))>0,1))

    So I am needing to figure out a way to combine the formulas so that I can use the same formula in multiple cells but have different dates.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)