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 6. Total comments: 591

  1. Hi.

    I wanted to know how to write a formula in Excel for the following:

    Suppose there are variable names available in individual cells in a column in one sheet. Excel needs to count (and subsequently sum up) the number of times those names appear in a column in another sheet and display the answer in a cell in another sheet.

    Greatly appreciate your assistance on this.

    Regards,
    Ken

    1. Hi Ken,

      Your Countif formula may look similar to this:
      =COUNTIF(Sheet2!A2:A100, Sheet1!A2)

      Where column A in sheet2 is where all the different names appear, and A2 in sheet1 is an individual cell with a variable name you want to count.

  2. Time Criteria using counti if
    =countif(A1:A100,"<="&TIME(10,0,0))

    I want to know how many people arrived before 10 AM from that column.

    Excel is not giving the output.

    Would b great if anyone could help

    1. Hi Sourav,

      Try this one:

      =COUNTIF(A1:A100,"<="&TIMEVALUE("10:00 AM"))

  3. hi i want to use cell as a criteria by counti formula,
    and i could not find out solution. is it possible?
    if possible how i can do it

    1. Hi Kerem,

      Of course, you can use a cell reference as a criteria. In this case, you enclose the operator in quotation marks and add an ampersand (&) before the cell. For example:
      =COUNTIFS(A2:A10,">"&$D$3)

  4. Hi Svetlana, I would really appreciate any help you can give me with the following:

    I have 2 columns of data like this
    Column 1 Column 2
    Beer 1
    Wine 2
    Beer 1
    Beer 2
    Wine 3

    and so on,is there a way to calculate the data to get the total amount from column 2 per item from column 1?

    Thanks in advance,

    1. Hi Kel,

      You can use a SUMIF formula similar to this:

      =SUMIF(A1:A5, "Beer", B1:B5) where column A is the product name and column B is qty.

  5. Ms. Cheusheva

    I have a good one for you, I need to take and if column B has the word ALSE I need to count the number in column C and post the resulting total number. Any Ideas?

    Thanks for all the help

  6. Dear Svetlana,

    I need to create a countif formula that will allow me to start counting in multiple spots.

    I.E 0 - 0 - 0 - 0 - 100 - 100 - 0 - 200 I want it to count FOUR becuase the trend starts. I need this for multiple lines with different starting points.

    You can treat this as says and months it was a new product so we didnt have sales in the 1st 4 months, but sales started on the 5th month and we had two months with 100 cases the 7th month we didn't have any sales and the 8th month we had 200cs. - I.E we averaged 100 cases per month. 400 cases in four months.

    the next item may be

    I.E 0 - 0 - 50 - 0 - 100 - 100 - 0 - 200

    Please help. I have tried everything i can think of.

    1. Hello Tom,

      If my understanding is correct, you need a Helper Column to fulfill this task. For example, if your data are in columns A:H, then in cell I2 you need to place the following array formula (use CTRL+SHIFT+ENTER to enter it):

      =COLUMNS(A2:H2)+1-MIN(IF(A2:H2>0,COLUMN(A2:H2),9999))

      Where A2:H2 are the cells with data. You will get the number of months from the sales start (4, 6, etc.).

      Then copy the formula down across the column I.

      After that you can calculate Average in the adjacent column using this formula:
      =sum(A2:H2)/I2

  7. Hi, I keep getting a value error for the following formula. Column E contains data from a validation list so I am not sure if this is affecting the outcome. Column K has words written as RED, GREEN, AMBER. Essentially I want to know how may meet the criteria of both Live and Red.

    The validation data is held in a worksheet entitled Sheet 3 - cell range D5-D9.

    =COUNTIFS('Programme Risks'!E4:E944,"Live",'Programme Risks'!K4:K920,"RED")

    Thanks in advance.

    1. Hi Ritchie,

      All ranges in COUNTIFS formulas must have the same number of rows and columns. So, I think changing the second range to K4:K944 should fix the problem.

  8. Thanks Svetlana,

    and if I wanted to use a cell reference instead of 60 do I just use "<C1" for instance?

  9. If i want to count "P" , "Late" and "Half Day" in same cell what will be the formula using countif formula.

    1. Hi Runu,

      To sum values with the OR logic, you need to add up 3 SUMIF functions, like in
      Example 4. COUNTIF formulas for non-contiguous ranges (OR logic)

  10. I have a problem with a "countifs" statement and I was wondering if anyone can help.

    One of my columns has cells where the content is <=5 as the value and it isn't a calculation. I want my countif to count how many instances of those, but when I put it in my countif calculation it sees the <= as part of the calculation and produces the wrong results. Is there any way to see <=5 as a value instead of a calc in that cell?

    Any help is appreciated.

    Thanks,
    Sharon

    1. Hi Sharon,

      I cannot figure out a way with COUNTIFS either. But you can use the following array formula instead:
      =SUM(--(A1:A100="<=5"))

      Please remember to press Ctrl+Shift+Enter to enter it correctly.

  11. Evening Svetlana,
    I have used your posts many a time to gain answers for my questions and until now was always able to find an answer. I have an issue that I cannot figure out. What I am looking for is a way to count the number of records that meet multiple criteria. My issues comes when adding in the multiple fields. I have a project list with Start dates, Team name, Department and Type of project. Above that I have a list of the start and stops for each week of the year.
    What I am trying to do is count the records where the start of the project is between the start\stop date for the given weeks but only when the project team is a given name (Blue, or Green) or the Department is a given name (SRT). The fields are exclusive but complimentary so I need to add an "or" statement but cannot get it to work.

    1. Hi Thomas,

      To count with the OR logic, you need to add several SUMIF or SUMIFS functions, for example:

      =COUNTIFS(A2:A10, ">=start date", A2:A10, "<=end date", B2:B10, "team1",) + =COUNTIFS(A2:A10, ">=start date", A2:A10, "<=end date", B2:B10, "team2",)...

  12. i have a sheet in which a column shows percentage of attendance i have to provide the formula for next column to give the values for the percentage..for example
    if the student have percentage in between 75 to 80 i have to give 01 marks and 81 to 85 then 02 marks ,86 to 90 03 marks etc.the how to write formula?

  13. Hi There,

    I am not able to find the right countif formula to count "AA" in next column with reference to another column!! For example in Column 1 I have different Cable types and in next four columns whether is applicable or not I had mentioned with AA, BB, CC, DD. Here I have to count how many "AA" are available against one type of Cable. Could any one please help me out??

    1. Hi Kardon,

      If column 1 (let's say column A) contains unique cable types only, i.e. a single record for a certain type, you can use the following formula:
      =COUNTIF(B2:F2, "AA")

      Where columns B - F contain AA, BB, CC, DD. The formula counts the number of AA in each row.

      If you have multiple instances of the same cable type in column A, then the best solution I can come up with is this lengthy formula:

      =COUNTIFS(A2:A8, "cable1",B2:B8, "AA") + COUNTIFS(A2:A8, "cable1",C2:C8, "AA") + COUNTIFS(A2:A8, "cable1",D2:D8, "AA") + COUNTIFS(A2:A8, "cable1",E2:E8, "AA") + COUNTIFS(A2:A8, "cable1",F2:F8, "AA")

      Where "cable1" in the cable type, 2 is the first and 8 is the last row with data.

  14. I'll explain what I have:
    I created color function to count cells with certain color and now I want to count celles that has value and certain color at the same time

  15. Hi!

    How can I use countifs and vlookup together?

    Thank you.

  16. This time with name...
    Hi!

    I need to be able to count the number of columns that have a date that is less than 11 days old. I understand I could do this using the Today() function, but it keeps hanging. I currently have =COUNTIFS(Infrastructure!$A$2:$A$2000,"Infrastructure",Infrastructure!$C$2:$C$2000,"<11"(&TODAY()-$C2)
    Where infrastructure is the name of the tab to go to, the first criteria says if the cell is 'Infrastructure', then the problem is the last calculation. I want to count the number of occurences where today minus the date in cell c2 is less than 11. CAn you help?

    1. Hi Jennifer,

      If my understanding is correct, you need just need to tweak the formula a little bit:
      =COUNTIFS(Infrastructure!$A$2:$A$2000,"Infrastructure",Infrastructure!$C$2:$C$2000,">="&(TODAY()-11))

  17. I am trying to count the amount of cells that list "Baltimore" in column H and the Agents name in column F. So basically how many lines in a sheet that contain a name AND a particular client.

    I tried =COUNTIF(Eval_Data!F:F,BRL_Agent_Summary!C4)+COUNTIF(Eval_Data!H:H,”Baltimore”)

    I fount that this will count the agents name but every instance that agents name appears and also if Baltimore appears.

    I also tried =COUNTIFS(Eval_Data!F:F,"C4",Eval_Data!H:H,”Baltimore")

    But it isn't counting the cells I want. It is bringing back 0 when I know there is data. I'm not sure what is wrong.

    1. Hi Joanna,

      Try the following formula:
      =COUNTIFS(Eval_Data!F:F,BRL_Agent_Summary!C4,Eval_Data!H:H,"Baltimore")

  18. I am working on a spreadsheet with a basic countif formula thats working well for me. When I add a filter to the worksheet in column A and filter by one item (e.g Apple) the formula does not autmatically updated. Please help.

    1. Hi Sham,

      The point is that the COUNTIF function processes all cells within a specified rage, not only those that are visible at the moment.

  19. Hi
    I hope you can help me. I am trying this formula as follow.

    =COUNTIFS('Sheet1'!$AV:$AV,""&"",'Sheet1'!$AR:$AR,"*Feb*") +
    COUNTIFS('Sheet2'!$AV:$AV,""&"",'Sheet2'!$AN:$AN,"*Feb*")

    I need to know contacts per month. If AV is not empty and AR in sheet1 / AN in sheet2 have a month, count it( they could be empty, so count only the ones that have months on them ). As simple as that.

    It doesn't return any value when I use ""&"", or "*".
    The format of both is "text" and the range as is the same.
    It is not working and I can not see what I am doing wrong.
    thank for helping!!

  20. Is it possible to use the COUNTIF option when using fractions? I have a column of several lengths (32", 36", and 48"). I know the formula to count the cells - but I'd like to count the cells in the same column that only have 32" lengths in the cell. I tried the following and it did not work.
    =COUNTIF(A3:A25,32")
    =COUNTIF(A3:A25,32)
    =COUNTIF(A3:A25,"32")

    Thank you!!!!!

    1. Hi Becky,

      Try this formula:
      =COUNTIF(A3:A25,"32""")

      I know it looks very unusual, but it works : )

  21. Hi,

    Column A: dates
    Column B: numbers

    I would like to calculate the number of times values in column B within a specified range occur within a specified date range. I've emailed an example file to support.
    Any help would be much appreciated.

    Many thanks,
    Dan

    1. Hi Dan,

      I believe the following formula will work for you:
      =COUNTIFS($B$2:$B$75, ">=6", $B$2:$B$75, "<=7", $A$2:$A$75, ">="&F2, $A$2:$A$75, "<="&G2)

      I emailed you the worksheet with the formulas a moment ago. Hopefully, this is what you are looking for.

  22. I have a workbook with multiple worksheets of inventory from various departments. I'm trying to create a summary page that sums the different categories of inventory from each worksheet tab. For example, how many desks do we have in total.

    My worksheets are set up like this:
    Desks. 26
    Chairs. 57
    Cabinets. 1

    But the info isn't in the same cells on each worksheet.

    Is this possible? Im p.yaong with countif but can't get it to work.

    1. Hi Dean,

      An immediate solution that comes to mind is using a combination of SUMIF functions like this:

      =SUMIF(Sheet2!A2:A7, "desks", Sheet2!B2:B7) + SUMIF(Sheet3!A6:A17, "desks", Sheet3!B6:B17)

      Where column A contains the categories of inventory and column B - the corresponding numbers. You can specify different ranges and different columns for each sheet.

      For more information about the SUMIF function with multiple criteria, please see this tutorial:
      https://www.ablebits.com/office-addins-blog/excel-sumifs-multiple-criteria/

  23. Hi Swetlana,
    Hope you doing well.
    I am having difficulty with formulas of multiple critearia.

    My formulas below is worked with me.
    =Countif(ACTL!B3:B50001;A$1;ACTL!$E$2:$E$50000;B$2;ACTL!$G$2:$G$50000;$B7)
    I want to add one more criteria which is date period.My Dates coloumn is starting from C1 till C1000.
    The target date is at B1(15oct2014)
    How can I continue to my formulas which will give me the total after 15Oct2014.
    If you can help me on it will be appreciate.

    Thanks & Regards.
    Utkan

    1. Hi Utkan,

      All ranges in COUNTIFS formulas should have the same number of rows and columns. Most likely, you need this formula:

      =COUNTIFS(ACTL!B3:B50001;A$1;ACTL!$E$2:$E$50000;B$2;ACTL!$G$2:$G$50000;$B7, ACTL!$C$2:$C$50000;$B$1)

  24. Dear Mam ,
    I will appreciate to resolve my problem.
    i have different date in column which i required the count where dates is mentioned and rest blank not count .
    Please help me

    Center Revised Date 2nd PM Plan Date 3rd PM Plan Date Total Count

    Bannu-1 1-Oct-14 1-Oct-14 Blank ?(Answers should be 2)

    1. Hello Muhammad,

      You can use this formula to count all non-blank cells:
      =COUNTIF(B2:F2,"*")

      To count only dates and numbers greater than 0, please use the following array formula (you have to press Ctrl + Shift + Enter to complete it):

      {=SUM(--NOT(ISERROR(DATEVALUE(TEXT(A2:F2,"mm/dd/yyy")))))}

      Where A2:F2 is the range you want to count.

  25. I am trying to take 2 tables - Galaxy Theatre and Empire Theatre. Each table contains a list of the theatres and the gendre of movie playing in the theatre. I need to count the number of horror movies playing in each theatre (provide a total for each theatre) using one equation. Is this possible?

    The question suggests using an indrect to help, but I cannot get the Indirect function to work with countif.

    1. Hello Nancy,

      You can use the following formula to sum the results:
      =CountIf(GalaxyTheatre[Genre],"Horror") + CountIf(EmprireTheatre[Genre],"Horror")

  26. Svetlana and team – I enjoyed reading your blog about the “COUNTIFS” function, but am having problems using it. I have a worksheet that I want to count the number of unduplicated rows with two different criteria. I want to count the number of completed actions by analyst, but only count each class code once. The class code is in column “G”, the analyst (there are five different analysts) is in column “C”, and the analysis/recommendation is in column “AF”. When an analysis is completed for a particular class code, the column “AF” has some text. If the analysis is not complete, “AF” is empty.

    I can successfully count the number of unduplicated class codes in my worksheet, by using:
    =SUM(IF(FREQUENCY(MATCH(G2:G7497,G2:G7497,0),MATCH(G2:G7497,G2:G7497,0))>0,1))
    My worksheet has 7,495 rows of duplicated class codes and 3.666 rows of unduplicated class codes.

    I can successfully count the number of analyses completed by analyst, by using:
    =COUNTIFS(C:C,"LF",(AF:AF),"")
    What I’m trying to do, but can’t figure out is how to use the COUNTIFS function and count the UNDUPLICATED number of analyses completed.
    Can you help?

    1. Hello Lorna,

      You can use the following array formula, please don't forget to press Ctrl + Shift + Enter to complete it:
      {=SUM((IF(FREQUENCY(MATCH(G2:G7497,G2:G7497,0),MATCH(G2:G7497,G2:G7497,0))>0,1,0))*(--(AF2:AF7498<>"")))}

      But I'd recommend adding a helper column and copying the class codes there according to your conditions, e.g. here is the formula for row 2:
      =if(AF2<>"", G2, "")
      Then copy this formula down to the other cells in the helper column and count using your first formula. This seems to be a quicker and more flexible approach.

      1. Svetlana - it WORKED! Many many thanks from California! Where do I send the Bailey's????

        1. Thank you, Lorna. A virtual bottle will suffice : )

  27. I have tried using the same multiple if's with a table that has multiple data validation lists. The user simply picks under the source column where the lead came from and then in the type column they would pick what type it came in as.

    My formula is =countif(Table1[Source],"Kijiji",Table1[Type],"phone") to find out what percentage of leads were sourced from kijiji by phone as opposed to kijiji by email or our website by phone.

    Not sure what I am doing wrong because it keeps throwing it out saying it is causing arguments.
    Any help would be appreciated.

    1. Hi Jason,

      You were almost there. It just should be COUNTIFS rather than COUNTIF because you count by 2 conditions:
      =COUNTIFS(Table1[Source],”Kijiji”,Table1[Type],”phone”)

  28. Hello,

    =COUNTIFS('Current Enlisted By Name'!$E:$E,LEFT(B6,4)&"*",'Current Enlisted By Name'!$D:$D,"WHQK"&"*")

    I need that formula above to capture these UICs only:

    WHQKA0,WHQKB0,WHQKC0,WHQKE0,WHQKT0,WHQLC0

    Please help

    1. Hello Rich,

      This can be done either with a helper column or VBA.

      If adding a helper column is acceptable, say column K and copy the following formula there, starting from row 2:
      =OR(D2="WHQKA0",D2="WHQKB0",D2="WHQKC0",D2="WHQKE0",D2="WHQKT0",D2="WHQLC0")

      Your main formula will be:
      =COUNTIFS('Current Enlisted By Name'!$E:$E,LEFT(B6,4)&"*",'Current Enlisted By Name'!$K:$K,TRUE)

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