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 16. Total comments: 2071

  1. Thanks Alexander for your reply.

    The error I get is

    https://support.content.office.net/en-us/media/38b1b648-6844-4b27-80db-638e4b8f225c.png

    The formula I'm using is the one I put in my original post above and there are no semicolons in sight. I'm not sure what you mean by "...what sort of quotes are you using." All I can say is the quotes are those used in the aforementioned formula which again is

    =SUM(COUNTIFS(B$5:B49,("RICHARD","ALAN")))

    I hope this clarifies things and I look forward to your suggested solution.

    Many thanks

    1. Hello!
      If you specify 2 criteria for COUNTIFS, then you need to do this as described in this manual.
      Or you can write these criteria as an array.

      =SUM(COUNTIFS(B$5:B49,{"RICHARD","ALAN"}))

      I think that was your problem.

  2. Hi

    I'm having difficulty with one of your suggested formulas and in particular one which is covered under heading

    https://www.ablebits.com/office-addins-blog/excel-countifs-multiple-criteria/#count-cells-multiple-criteria-OR-logic

    and sub heading.

    Formula 2. SUM COUNTIFS with an array constant

    The formula is 'SUM(COUNTIFS(range,{"criteria1","criteria2","criteria3",…}))' and it just isn't working for me. I keep getting a warning that it isn't a valid formula.

    Is there a minimum Excel version that this only works with?

    I'm sure I'm applying it correctly but just in case I'm missing something I have a table 49 rows high and column B contains a list of 6 different names which appear randomly and are repeated randomly. Two of the names are Alan & Richard who both appear numerous times so I created the following formula to count the rows that contain either

    =SUM(COUNTIFS(B$5:B49,("RICHARD","ALAN")))

    But, this doesn't work. Please let me know what I'm doing wrong.

    Many thanks.

    1. Hello!
      This function is available since Excel 2007. Please describe your error in more detail. Your Windows may not be using a comma as the list separator, but a semicolon. Also note what kind of quotes you are using.

  3. =COUNTIFS('Loading Summary'!A:A, B3, 'Loading Summary'!E:E, "*Y*") This works,

    but this doesn't work =COUNTIFS('Loading Summary'!A:A, B3, 'Loading Summary'!E:AH, "*Y*")

    I want to give the range from Column E to Column AH to check if there's letter "Y" for a specific Unique ID in Column B3 and count how many are there

    1. Hi!
      Pay attention to the following paragraph of the article above — Excel COUNTIFS - things to remember. It contains answers to your question.

  4. Hi, I realized I have exactly the scenario that can't be covered by COUNTIFS (point 2 under Things to Remember): "Each additional range must have the same number of rows and columns as the first range (criteria_range1 argument)."

    Illustration for simplicity: Col A has country values. Col B to D have 3 survey question responses.

    With a simple COUNTIF, I can count how many "Strongly Agree" from B to D.

    But I'm trying to those who "Strongly Agree" from "USA" only.

    The naive way is to expand Country to 3 columns so that the range matches, but is there a better way? Also, I'm dealing with..... OMG about 100 columns PER question. My column names are in the triple alphabets!

  5. Which formula do I use if I want to count the number of entries in one column that refers to a specific month (e.g. how many entries for the month of March) that also has an entry (of any written text) in another column (this will be a column that refers to a specific behaviour but could be varied for each row e.g. physical aggression in one row but verbal aggression in another row)?

    Hope you can help.

    2. And to clarify a bit further, I do not want to know how many physical aggressions or verbal aggression entries there are individually but just how many entries appear in that column for a particular month altogether.

  6. Hello! and thanks a lot for your response. I have tried all possible options of countifs I thought to have understood... the question is; have I done it correctly....? I doubt it.... :(

    From your suggestion, I tried

    =SUM(COUNTIFS(range,{criteria1,criteria2,criteria2})
    The range columns "B:T" and (criteria, branch, call outcome and moth)=0

    Selected all the range where the data is and choosing criteria 1,2 &3; Branch, month, and call outcome... but if this option can be used in this scenario, would the range for the constant array be grouped separately from the other two criteria?

    Could it be that countif is not the right formula? I have looked into vlookup, xlookup and sumproduct but I am not sure they are the right formulas.

    I have listed the data below, I hope this helps as no option to paste a screenshot here.

    CALL OUTCOMES - (these are 15 outcomes to choose from; listed below, in columns (I,L,O,P,Q,R,S,T) each client may have a different outcome each time in the 8 calls.

    Call other time (see notes)
    FE - Incorrect number
    FE - Message left on machine
    FE - No answer
    FE - Pt not in
    FE - Pt. moved out
    FE - Phone unobtainable
    First Consultation
    FU
    FE FU - call another time
    FE FU - No answer
    FE FU - Pt. Abroad
    FE FU - Pt. not in
    FE FU - Unwell
    FE - Decease

    BRANCH is in column F
    DSDK
    EEMC
    SHC
    TFP
    ULMC

    MONTH in column B

    Thanks again, for looking into this. I hope this makes the question a bit clearer.

    1. Alexander, thank you so much for your help!! I am so grateful and very impressed with how kind and patient you have been.

  7. Hello, and thank you in advance for your help. I need to collate from a data set of a calling center the following:
    1. Branch
    2. Month
    3. Call outcomes collected 8 colums (I,L,O,P, Q,R,S,T). There are 12 different call outcomes eg (didn't answer, person not in, etc) so, the report tells me:
    In April, Branch "A" reported that 10 calls weren't answered, 5 the person wasn't in and so on
    I have tried COUNTIFS but does not do the trick... I get VALUE error. Is this because one criteria is counted several times in different columns some of which are adjacent? or this is not the formula i should use?
    I hope this makes sense, thank you!

    1. Hello!
      Without seeing your data it is difficult to give you any advice. If I understand your task correctly, pay attention to the following paragraph of the article above — Add up two or more COUNTIF or COUNITFS formulas.
      If this advice does not help, write what formula you use and what exactly you want to calculate. It’ll help me understand it better and find a solution for you.

  8. Hi there,

    I am having a dataset of people contacting me with Hours of the day (ColA), Date (ColB). Now I want to create a table where I will see how many times in that hour someone contacted me so I can create a hit map. I am quite struggling with what formula to use which will return the number of a contact in that hour of the specific day. Any help?

    1. Hello!
      If you want to get a list of contacts for a specific day and hour, then use the FILTER function. Read this detailed guide.
      If you want to count how many times a person has contacted you at a certain time, then use the COUNTIFS function. Read this article above.

  9. Hi,

    My query might be basic but I am not able to solve it.
    I have the below table (part of a large database). The first column is product type and the rest columns are product SKUs with quantities in data fields. There are 2 blanks (3rd column 4th cell and last cell of the table). I want to find how many non-blank cells of each product type are there. "-" is a non-blank cell but the quantities are not clear. A blank cell simply means there is no such SKU for that product.
    I was thinking of COUNTIFS but cannot create anything.

    Please help. Thanks a lot.

    Type I II III IV
    S 25 - 50 -
    I 25 12 2 12
    S 1 - 2
    A 25 - 33 21
    I 12 - 1

      1. Thanks, Alexander.

        But that example has only one type of data (i.e. numbers) on which there are conditions.
        In the table I have, the first criterion is matching the text and then other criteria have to check against numbers, special characters, and blanks. A simple countifs won't work I guess. It has to be a combination of other functions as well.

        The answer I am looking for would say something like:
        Type S and type I have 7 non-blank data fields and Type A has 4 non-blank data fields.

        Thanks.

    2. just to clarify, in the third last row, the quantities are 1, blank, -, 2 and the last row has quantities 12, -. 1, blank.

  10. If I have a sheet that contains dates in Column B (Heading row for Column B has a start date in B1 and End date in B2) and multiple other columns, one (Column L) contains specific words.
    How can a count the number of specific words, in Columns L that fall between 2 dates in Column B?

    For example I have dates from January 1 to March 31 in Column B. I have the word Weston in Column L multiple times. I want to find out how many times Weston between between January 10 to January 16.

    I've tried Countif, Countifs, if(and, if(or, nested if. I am pretty good with excel and teach it, but this one is stumping me.

    Sylvia

    1. Hello!
      Please use the following formula/the formula below to solve your task:

      =COUNTIFS(B1:B10,">="&DATE(2021,1,10),B1:B10,"<="&DATE(2021,1,16),L1:L10,"Weston")

      Read this tutorial on how the COUNTIF function works with dates.

      1. Thank you very much. This had been a great help. I will be definitely be referring back to you if I have any other questions.

  11. Hi Im trying to look for a formula for this scenario, I have product a, b,c,&d (up to z or more)

    each product can fall in different levels that are updated daily between level 1,level 2 and level3. Is there a formula that can generate daily(todays date) numbers per level for each product?

    I hope you understand what Im trying to say

    logged as

    PRODUCT column 1 , LEVEL column2 , DATE column 3

    a level 1 march 24

    b level 2 march 23

    c level 3 march 24

    d level 1 march 22

    HOPING OUTCOME in separate sheet)

    UPDATE FOR TODAY DATE MARCH 24 (assuming)

    Column 1 level , column 2 total number of account that fall on todays date

    LEVEL 1 - 1

    LEVEL 2 - 0

    LEVEL 3 -1

    Thank you

    currently my brain is twisted and for me to generate that is to update manually the range daily =COUNTIF(V1581:V2983,"LEVEL 1") which is very hustle because I need to change all levels (16) daily range and rearrange all products that fall on the todays date

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

      =COUNTIFS(B2:B100,"level 1",C2:C100,TODAY())

      I hope you have studied the recommendations in the tutorial above. It contains answers to your question.

      1. Thank you,ive tried rhis formula before but unfortunately
        Excel always Pops up youve entered too many arguments for this function

        This is a big big help ill try to look for errors in my template

        Thank you so much

  12. I am using a COUNTIFS function to build a report but need one of the many criteria to be a calculation. Specifically I need one of the criteria to count if the Completion Date column is a date after the Due Date column. The formula will be modified to also count when Completion Date ">10" days after Due Date to be counted. How do I embed this calculation comparing dates in a COUNTIFS function?

    It is a large data set of over 1,000 rows and it is for a daily report, so row numbers vary daily but column headings do not change. The dashboard tab is where the formula will reside but the COUNTIFS function pulls from different tabs in same spreadsheet if that matters.

    Thanks!

    1. Hello!
      Unfortunately, without seeing your data it is impossible to give you advice.

      I'm sorry, it is not very clear what result you want to get. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.

      We'll look into your task and try to help.

  13. Hello,

    I'm pulling my hair out. I'm trying to count the number of cells where one range contains specific text and another range is less than today's date. I need both conditions to be true before the cell can be counted. The formula below is not working. Should I use a function other than COUNTIFS?

    =COUNTIFS(C6:NH6,"*-B*",C3:NI3,"<"&TODAY())

    1. Hello!
      The ranges of criteria in the COUNTIFS function must be the same size. For instance:

      =COUNTIFS(C6:T6,"*-B*",C3:T3,"<"&TODAY())

  14. pls help me for the below.
    A series of numbers repeated many times in a database for certain day. How to count the repeated occurrence of the series of numbers on that particular day in the database.

    1. Hi,
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get? Give an example of the source data and the expected result.

  15. Question:
    For the table below I'd like to count the number of staff leads who had at least one encounter in a specific time frame and the number of these who's last encounter was in a specific region

    I'd like to accomplish this using a single cell formula/array formula with no helper cells/columns.
    If it helps in understanding the formula feel free to show the helper cells but use of helper cells is not the way I'd like to do this -- since the table/worksheet is dynamic and i'm interested in using workbook real estate for other data.
    I'm not good at vba, so, while it may another option it would be a steeper learning curve for me to understand and implement.

    ex:
    1. count number of team members who had an encounter between 7/1/20 and 6/30/21, then
    2. report the number of leads whose last encounter within the time frame was in the MidWest
    expected ans:
    1. 9/10
    2. 3/10

    Date Region Lead Customer Product
    10/14/2021 MidWest* Tina Amazon CIN Item
    1/7/2021 SouthEast Tina Google Doc COL Item
    10/26/2020 PureWest Chin High Def AIM Item
    5/20/2020 PureNorth Chin OverDone AIM Item
    9/24/2020 MidWest* Sue High Def RAD Item
    1/14/2021 MidWest* Fran Economist AIM Item
    4/3/2020 PureWest Tina High Def RAD Item
    1/20/2021 NorthEast Batt Economist DAB Item
    2/26/2020 SouthEast Tina H and eMA RAD Item
    12/16/2021 MidWest* Gigi OverDone AIM Item
    4/5/2020 NorthEast Batt Amazon AIM Item
    6/24/2020 NorthEast Tina Yahoo Mag RAD Item
    8/22/2021 SouthEast Chin McLendon's RAD Item
    11/9/2021 NorthEast Pham Costco's CIN Item
    3/23/2021 MidWest* Fran High Def XOL Item
    10/3/2020 MidWest* Sue Peet's Nut RAD Item
    4/4/2021 MidWest* Batt Amazon AIM Item
    1/5/2020 NorthEast Shelia High Def CIN Item
    11/6/2021 NorthEast Gigi Women's AIM Item
    9/25/2021 SouthEast Sioux Amazon XOL Item
    11/12/2021 MidWest* Sioux Economist RAD Item
    12/7/2020 PureWest Gigi Google Doc CIN Item
    11/5/2021 PureNorth Sue Quality FC AIM Item
    9/30/2021 NorthEast Gigi Peet's Nut CIN Item
    8/27/2020 NorthEast Fran H and eMA AIM Item
    8/18/2021 NorthEast Gigi OverDone XOL Item
    2/19/2020 PureNorth Tina H and eMA RAD Item
    12/11/2021 NorthEast Gigi McLendon's AIM Item
    3/17/2021 NorthEast Pham McLendon's COL Item
    12/1/2020 PureNorth Sioux Economist RAD Item
    4/5/2021 NorthEast Fran H and eMA NEE Item
    6/4/2020 MidWest* Sioux Google Doc AIM Item
    7/19/2021 PureWest Sioux Google Doc AIM Item
    4/25/2020 PureNorth Sue High Def COL Item
    9/24/2021 PureWest Sue OverDone AIM Item
    6/17/2020 MidWest* Tina McLendon's NEE Item

  16. Hello!

    I am trying to get a formula that would count the cells in column c2:c150 that are greater than 400, less than 500, and the cells in column H2:H150 equal "10"

    Can this be done?

    Thank you in advance for your assistance!

    1. Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question. Please specify what formula you used and what problem or error occurred.

  17. hello,
    i need a little help :)
    i have a excel file with some documents with barcode and date+time when are created and i need to do somehow so that i know how many documents are before 14:00 PM.The format of the cell is like this 16.02.2021 17:41:37(all this are in a single cell).I can split date and time but i dont know what formula tu use.tks

    1. Hello!
      To calculate how many documents are before 14:00 today, use the formula:

      =SUM(--(A1:A10<=(TODAY()+TIME(14,0,0)))*(A1:A10>TODAY()))

      I hope I answered your question. If something is still unclear, please feel free to ask.

      1. hello,is not working.i think because in my collumn i have data+time(in the same collumn),i cam split them but then i dont know :).
        can we somehow comunicate via email? i want to sent you my excel file :)

        1. Hi,
          The formula I sent to you was created based on the description you provided in your first request. The cell contains date + time. Everything works. Perhaps your data is not recorded as date and time, but as text.
          Send us a small sample workbook with the source data and expected result to support@ablebits.com. Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.
          We'll look into your task and try to help.

          1. Hello,
            i send the email,tks again.Have a nice day

    2. i forgot to say that i could have hundreds of document in the same day so date is the same just time is different

  18. What if I need to validate the "Sheet No MP1" from a dropdown list for the below criteria

    =COUNTIFS($E$5:$E$10,"=Posted", $F$5:$F$10,"=Sheet No MP1")

  19. Hello - I am currently using this formula to search for a single value in a range of cells:
    =IF(COUNTIF(AS2:AU2,"*" & "Help Center" & "*"),"True","False")

    However, rather than matching to a single value of "Help Center" with wild card existing anyplace within AS2:AU2, I want to search for multiple values so I don't have to repeat the search multiple times. The values I want to search for are:

    Help Center
    helpcenter

    Can you help?

    1. Hi,
      I’m sorry but your description doesn’t give me a complete understanding of your task. Correct me if I’m wrong, but I believe the formula below will help:

      =IF(COUNTIF(AS2:AU2,”*” & A1 & “*”),”True”,”False”)

      After that you can copy this formula down along the column.
      A1 -- Help Center
      A2 -- helpcenter etc.
      If this is not what you wanted, then describe the problem in more detail.

      1. Thanks for the response. This is a long spreadsheet that gets sorted frequently or filtered. Ideally the search values would be on another tab, and not added into a row on the current tab.

        Something like this, with a filter:
        =IF(COUNTIF(AR2:AT2,"*" & !Filter,C1:C7" & "*"),"True","False")

        I also tried this, but it gives a Spill error:
        =IF(COUNTIF(AS2:AU2,"*" & {"Help Center","groups.xxxx.com","https://xxxxx.service-now.com/sp","helpcenter","help.xxxx.com"} & "*"),"True","False")

        For example, I have this forumula in another column, and tried to use a similar filter condition with the other command, but can't get it to work.
        =IF(ISERROR(VLOOKUP(T2,Filter!$A$2:$A$100,1,FALSE)), "NO","Collaboration")

        Is there a way to use countif with wild card and referring to a list of values on another tab?

        Thanks again!

        1. I think I just got it! Thanks so much:

          =IF(OR(COUNTIF(AS2:AU2,"*" & Table1[Portals] & "*")),"Out of Scope","False")

  20. I have a rather large table and I'm trying to highlight rows that meet multiple criteria. Yes I'm a novice. In column CY, each cell has multiple words (for instance: Fire Damage, Split Top, Decayed Top); in column Q each cell has a date (year only). I'm trying to pinpoint and highlight the rows that contain the words "Split Top" in column CY and a date of 1995 or higher in column Q. Every formula I've tried highlight the rows if they're carrying one or the other, or both. For reference, I've highlighted the entire table and went to conditional formatting and entered the following:
    =countifs(($CY2,"*Split Top*")+($Q2>1995))
    Can you help?

    1. Got it! I never put the comma between Q2 and the parenthesis. Thanks!

      =COUNTIFS($CY2,"*Split Top*",$Q2,">1994")

  21. ok, so im trying to figure out how to add 2nd variable onto my formula to count them both. but i cant get anything to work.

    =COUNTIFS(DUE!A:A,"DLA",DUE!C:C,"250",DUE!N:N,"V")

    now this currently works, but i also want to add Y that is also in the same column as "V"

    any help would be greatly appreciated

  22. Create a formula that displays the coupon amount for each customer based on department. Remember! The standards of professionalism require that you DO NOT include numbers in formulas if the numbers might change in the future. These coupons will change in the future.

    Electronics—$25 coupon.
    Toys—$20 coupon.
    Sports—$15 coupon.
    Shoes—$10 coupon.
    Every other department—$5 coupon.

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get? Give an example of the source data and the expected result.
      Thank you!

  23. Status Group Name
    A B C
    1 Solved Everglow Mike

    2 Closed GreenDot Jess

    3 Pending Pledis Mike

    4 Closed Pledis Gus

    5 Solved Greendot Jess

    6 Solved Greendot Charlie

    7 Pending Everglow Gus

    I wanna count how many Gus, Jess, Mike has a Solved or Closed Status from Greendot in to one cell. when i have the formula below the error #spill! shows..

    =COUNTIFS(C:C, {"Gus","Jess,"Mike"}, B:B, "Greendot", A:A, "Solved")+COUNTIFS(C:C, {"Gus","Jess","Mike"}, B:B, "Greendot", A:A, "Closed")

    please help i'm very confused

    1. i figured out the formula

      =COUNTIFS(C:C, "Gus", B:B, Greendot, A:A, "Solved")+COUNTIFS(C:C, "Gus", B:B, Greendot, A:A, "Closed")+COUNTIFS(C:C, "Jess", B:B, Greendot, A:A, "Solved")+COUNTIFS(C:C, "Jess", B:B, Greendot, A:A, "Closed")+COUNTIFS(C:C, "Mike", B:B, Greendot, A:A, "Solved")+COUNTIFS(C:C, "Mike", B:B, Greendot, A:A, "Closed")

      but honestly is there no way to shorten this formula?

      1. Hi!

        The formula below will do the trick for you:

        =SUM(COUNTIFS(C:C, {"Gus","Mike","Jess"}, B:B, "Greendot", A:A, {"Solved";"Closed"}))

        I hope I answered your question.

  24. I need a formula to count if columns A, B, C, OR D have "*apples*" AND column E is not blank.

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =SUM((ISNUMBER(SEARCH("apples",A1:A10,1))+ISNUMBER(SEARCH("apples",B1:B10,1))+ISNUMBER(SEARCH("apples",C1:C10,1))+ISNUMBER(SEARCH("apples",D1:D10,1)))*NOT(ISBLANK(E1:E10)))

      I hope my advice will help you solve your task.

  25. Hi, I doing a sale excel were I need to count all the same product multiply by the amount of order.

    Order product
    2. Apple
    3. Orange
    1 apple

    Were apple should be 3 and orange will be 3.

  26. Hi, I doing a sale excel were I need to count all the same product multiply by the amount of order.

    Order product
    2. Apple
    3. Orange
    1 apple

    Were apple should be 3

  27. Hi,

    Thanks for this article. My problem now is it doesn’t count when one cell has the same text, especially two names of the same person.

    The text goes like this:

    G2:G10 = James
    G11:G20 = James Pattinson

    Formula:

    =(COUNTIFS(F:F, $K$5, G:G,{"James", "James Pattinson"},D:D,"Done"))

    The formula refuses to count cells that have the same first keyword. Please advise on how to solve this.

    Paul

    1. Hello!
      I believe the following formula will help you solve your task:

      =SUM(COUNTIFS(F:F, $K$5, G:G,{“James”, “James Pattinson”},D:D,”Done”))

      or

      =COUNTIFS(F:F, $K$5, G:G,“James”&"*",D:D,”Done”)

      I hope my advice will help you solve your task.

  28. Hello Alexander,

    Works like a charm :D
    Thank you very much for your help.

    Kind regards,
    Mitchell

  29. Hello,

    In the example "How to use COUNTIFS with wildcard characters"

    How would I count the number of "Mike" in column B when the date in C equals the date in D?

    Kind regards,
    Mitchell

    1. Hello!
      Please try the following formula:

      =SUMPRODUCT((B2:B10="Mike")*(--(C2:C10=D2:D10)))

      Hope this is what you need.

      1. Hello Alexander,

        Thanks for your feedback. Unfortunately I don't get the desired result, but a "value not available error".

        So what I'm trying to do:
        On sheet 1 I have information on deliveries. (suppliers (column N), promise dates(column G), actual delivery dates(column H), and many more)

        On sheet 2 I have listed all suppliers(column A) and added a column(B) with a formula which counts the number of times a supplier made a delivery =COUNTIF(Sheet1!$A$2:$P$268;A2)
        Now I want to add a column(C) which only counts the times this supplier actually delivered on the promise date.(Sheet 1, G=H)

        With your help I tried below formula, which returns a "value not available error".

        =SUMPRODUCT((Sheet1!$A$2:$N$268="A2")*(--((Sheet1!$G$2:$G$268:Sheet1!$H$2:$H$268))))

        Based on above information, is there a formula which can calculate this without adding columns to sheet 1?

        Kind regards,
        Mitchell

        1. Hello!
          I wrote this formula based on the description you provided in your original comment. Please note that if you’d provided me with the precise and detailed description of your task from the very beginning, I’d have created a different formula that would have worked for your case.

          =SUMPRODUCT((Sheet1!$N$2:$N$268=A2)*(--(Sheet1!$G$2:$G$268=Sheet1!$H$2:$H$268)))

          I hope this will help

  30. Thanks, Svetlana!
    A lifesaver, as usual.

  31. Hi! Very useful article. I'm struggling with figuring out a formula.

    I've got two sheets: the first with raw data, the second with a summary table I'm trying to configure.

    Here's a screenshot of the summary table sheet: https://www.dropbox.com/s/2xpyrccqrgmpqe8/Capture%20d%E2%80%99%C3%A9cran%2C%20le%202021-02-05%20%C3%A0%2016.43.41.png?dl=0

    Here's a screenshot of the raw data sheet: https://www.dropbox.com/s/yb85pb8d759z4k0/Capture%20d%E2%80%99%C3%A9cran%2C%20le%202021-02-05%20%C3%A0%2016.44.08.png?dl=0

    I'm trying to use COUNTIF to count the number of entries that match the following:

    Data!A:A must match Summary!A7 (Grade
    Data!B:D must match Summary!B7 (Grade)
    Data!J must be between C3 and C4 (Date Range)

    I can't seem to figure it out for the life of me. I've tried SUM COUNTIFS with multiple criteria and data ranges to no avail. Any help/insight would be much appreciated!

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

      =COUNTIFS(J:J,">"&$C$2,J:J," < "&$C$3,A:A,A7,B:B,B7) + COUNTIFS(J:J,">"&$C$2,J:J," < "&$C$3,A:A,A7,C:C,B7) + COUNTIFS(J:J,">"&$C$2,J:J," < "&$C$3,A:A,A7,D:D,B7)

      In this formula, correct the cell references to match your data and worksheet names. I cannot verify its work, since I do not have your data. To understand this formula, I recommend reading the article above.
      I hope I have answered your question. If something is still unclear, please feel free to ask.

      1. It worked! Thanks Alexander!

        I was stumped with trying to match data across 3 columns, but your solution nailed it by simply adding them up. Thanks so much.

  32. Hi, How can I count the OR# per per loan type and status using countifs, below the sample

    STATUS LOAN TYPE OR#
    2 HP 123
    1 HP
    3 HP 456

    1. STATUS -LOAN TYPE-OR#
      1 - HP -123
      2 -HP
      3 -HP -456

      1. Hello!
        I hope you have studied the recommendations in the tutorial above. It contains answers to your question: COUNTIFS formula with multiple criteria
        Please check the formula below, it should work for you:

        =COUNTIFS(A2:A4,1,B2:B4,"HP")

        I hope it’ll be helpful.

  33. Hi, I have a cell range with a start date and a cell range with an end date and I'm trying to use the TODAY function to return the amount of people within the two date ranges when I load the sheet - any idea?

  34. Hi, I am trying to set up a spreadsheet to check whether colleagues are completing specific tasks (that are recorded on a different spreadsheet), at least once within each 28 day period and to count how many of the task they did within the period. The best way I can think of it to to pull the count for each date and then have a separate tab that then works off that count to be certain the colleague is competent over the year. But I'm struggling to get the correct formula. What I need is: the count if column M on spreadsheet A is equal to cell A2 on spreadsheet B AND the date within column Q on spreadsheet A is equal to B1 on spreadsheet B - spreadsheet A being the one that contains the original data and spreadsheet B being the new one I am setting up. Is there any way to do this?

    1. Hello!
      Without seeing your data it is difficult to give you any advice. You have not specified what data you want to retrieve into the new table.
      Please check out the following article on our blog, it’ll be sure to help you with your task:
      Vlookup multiple matches based on multiple conditions
      You can also solve your problem without formulas using a pivot table.
      I hope I answered your question. If something is still unclear, please feel free to ask.

  35. IF I PREPARED A ATTENDANCE SHEET I WANT TO COUNT P,D,C,O IN ONE CAN U TELL ME HOW

    1. easiest way to count this is by using formula Countif or Countifs. See the examples in the article.

    2. Hi,
      I’m sorry but your task is not entirely clear to me.
      Please describe your problem in more detail. What are P, D, C, O? Letters? Columns? Values? What do you want to calculate? Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you.

  36. Hi,

    I am trying to use countifs using OR logic to find the number of bacterial strains with my gene of interest.

    The formula I'm using is:

    =SUM(COUNTIFS(BB:BB,("*"&BO3&"*", "*"&BP3&"*", "*"&BQ3&"*") BO3, BP3 and BQ3 are all text cells

    The data range is filled with lines of text corresponding to all genes that are present for that sample, I am interested in knowing how many samples contain gene x, or gene y, or gene z.

    My issue here is that only the cell BO3 is highlighted as an actual reference cell in the formula, any of the other cells I try to reference after the first one (Ie BP3 and BQ3) does not show up as a referenced cell.

    It would appear that the formula only lets you have one wildcard, while all other criteria have to be exact matches? I hope this isn't the case and I'm just confused.

    Thank you in advance.

    1. Hello!
      Without seeing your data it is difficult to give you any advice. You spelled the COUNTIFS formula incorrectly. For each criteria, you need to specify criteria_range:

      COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

      I think you should use the COUNTIF function as described in this section above.
      I hope I answered your question. If something is still unclear, please feel free to ask.

  37. *executed Contract per month
    MONTHS ANN VAL GLENN BELLA JO SEB
    January
    February
    March
    April
    May
    June
    July
    August
    September
    October
    November
    December

    1. Hi,
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred.

  38. =COUNTIFS('C.O MONITORING'!X8:X992,"MOIA",'C.O MONITORING'!AE8:AE992,"BELLA")
    this is my old formula. counting the number of contracts executed by a certain person "Bella"

    for new formula
    I want to count the number of the contract executed by "BELLA" per months (with a specific date)

  39. how to use countif for one date range (mont and year only) and specific text

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get? Give an example of the source data and the expected result.

  40. Hi,

    I have data that shows me how many times different projects pass or fail, but I want to count how may times a project fails on different days, i.e. not to count it twice if it failed twice on the same day. See simplified data below; I want to return Project 101 as 'Failing' 3 times, i.e. on 3 days, not 4 individual occasions.

    A B C
    Date Project Result
    01 Jan 101 Fail
    01 Jan 101 Fail
    02 Jan 102 Fail
    02 Jan 101 Fail
    03 Jan 101 Pass
    03 Jan 101 Fail
    04 Jan 103 Pass

    Thanks in advance
    Paul

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

      =IFERROR(ROWS(UNIQUE(FILTER($A$1:$A$8, $B$1:$B$8=$D$1))), 0)

      where $D$1=101.
      You can learn more about count unique values with criteria in this article.
      I hope it’ll be helpful.

  41. I'd appreciate any help or insight.

    Use Case: Get count of multiple criteria from different columns

    Criteria1: =COUNTIFS('Sales Report'!$G:$G,"Damy Dams",'Sales Report'!$H:$H,"Nov",'Sales Report'!$I:$I,{"Movies","Music"}),

    Criteria 2: 'Sales Report'!$E:$E, {"Offline","Onsite","Podcast"})

    My formula returned the count for Criteria 1 BUT does not return the count for Criteria 2

    How can I combine all into one statement?
    For example: =SUM(Criteria1,Criteria2)

    AND NOT

    =SUM(Criteria1+ SUM(COUNTIFS(Criteria2)…. this gives a much higher count value and is not the requirement.

    1. Hello!
      Use the formula for Criteria2

      =SUM(COUNTIFS('Sales Report'!$E:$E, {“Offline”;”Onsite”;”Podcast”}))

      I hope it’ll be helpful.

  42. Hi,

    I need following assistance from you guys.

    In my data table column A = code number and column B = name.

    If code number in column A is same, that has to be count as one only.

    Count result should be Reza = 2, Jonny = 1, Rony = 2 & Bony = 1.

    Code Name
    438 Reza
    438 Reza
    438 Reza
    438 Reza
    438 Reza
    438 Reza
    438 Reza
    438 Reza
    438 Reza
    438 Reza
    439 Jonny
    440 Reza
    441 Rony
    442 Bony
    443 Rony

    Thanks in advance for you assistance .

    Warm regards,
    Hamid

  43. Hi, I am trying to count non text items listed in one column. These items belong to each patients who can have a cardiovascular risk that could be "<10%", "10 - <20%", "20 - <30%", "30 - 40%". How do I use countif formular for these to create how many patients for each CVR category?

  44. Hi, I am trying to set up a spreadsheet to check whether colleagues are completing specific tasks (that are recorded on a different spreadsheet), at least once within each 28 day period and to count how many of the task they did within the period. The best way I can think of it to to pull the count for each date and then have a separate tab that then works off that count to be certain the colleague is competent over the year. But I'm struggling to get the correct formula. What I need is: the count if column M on spreadsheet A is equal to cell A2 on spreadsheet B AND the date within column Q on spreadsheet A is equal to B1 on spreadsheet B - spreadsheet A being the one that contains the original data and spreadsheet B being the new one I am setting up

  45. How to combine following two formula..?

    if(and(f2>=d25,i2<=e25),b25,"")

    if(countifs(ar3:ar6,i24),"",b25)

  46. I have a file with some numerical grading,i need the lowest grading ...
    how can i calculate the same
    Row Labels- 2 3 4 5 1 6

  47. Hi,

    May be this is a silly question, but i cant figure it out the solution. Here's the data:
    A1 Korea
    A2 Indonesia
    A3 Indonesia
    A4 Singapore
    A5 Malaysia
    A6 Indonesia
    A7 Malaysia
    A8 Indonesia

    I want to calculate what is the most showed up country. Do you have any solutions? Thank you in advance

  48. Hey I want to concatenate names in excel ..but the conditon is

    In coloum A
    A1 is having an I'd and then second I'd number is present in another cell suppose A6 .. so I want excel to concatenate name from A1 to A5 ..and excel can do the same ..for entire sheet .with varying range of cells with I'd number

  49. I would like to find out how to group values using COUNTIFs in excel. For example, i have a grade sheet of students with the student name, subject and grade.

    Student Subject Grade
    Ann English A
    Ann Math A
    Joe English B
    Ann Science B
    Joe English A

    I want to group by the Student and the grade

    Student Grade Total
    Ann A 2
    Ann B 1
    JOE B 1
    Joe A 1

  50. A1 09:00:10 , A2 Medical , A3 11:03:25 , A4 blank
    what can I show B1 is 1 , B2 is 0 , B3 1 , B4 0 .

    1. Hi,
      Please describe the logic behind your calculations. What do you want to count? Why B1 = 1? Please specify what you were trying to find, what formula you used and what problem or error occurred.

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