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 8. Total comments: 797

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

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

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

  4. Hi!

    How can I use countifs and vlookup together?

    Thank you.

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

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

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

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

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

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

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

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

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

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

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

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

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



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