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 27. Total comments: 2055

  1. I'm having an issue here:

    for those names that fall under P:P may not match F4 exactly, because of an initial in the name, how would I write this to count if name is similar when looking up on different tab?

    =IFERROR(COUNTIFS('Issues w-out Action Plans'!K:K,"< 30",'Issues w-out Action Plans'!P:P,'MoEVM Summary Level'!F4),"0")

  2. Can someone help me make this formula work?
    =COUNTIF(B33,E33,H33,K33,N33,Q33,T33,W33,Z33,AC33,AF33,AI33,AL33,AO33,AR33,AU33,AX33,BA33,BD33, ">70%")
    I only want these cells condidered in the count if because they contain student data for a paticular test.

    1. Dawnica:
      The COUNTIF function should be setup like COUNTIF(Range,Criteria). So in your case if the data range is B33:BD33 and the criteria is values greater than 70 the formula looks like this:
      =COUNTIF(B33:BD33,">"&70)

      1. Thanks for the help!
        But what id there are cells in this row that I do not want included in the countif. is it possible to exclude cells?

        That is, in my formula attempt I wanted to get the number of students who scored above 70 on test 1
        When I put in the formula you provided it every cell in the row so it is counting scores above 70 for all tests given so far. Is there a way to do this or would I need a different type of formula entirely?

        1. Dawnica:
          What are the other criteria for the tests you want to count? Are they by date or a test number or something else?
          Which version of Excel are you using?

  3. Dears,
    I have three criteria in column "C", "D" and "E"to consider for customers. i want to have another column "F" wherein i would like to get one value after which i shall use the command. But how can i write an if statement which is basically"
    If the value in column "C" is zero, consider the value in Column "D" and if column "D" is zero, then consider the value in column "E".
    Thank you

  4. Hi. I'm looking for help writing a coutif formula. I have a column with a list of dates. I'm looking to count all dates that are greater than TODAY(). I don't know how to write the condition to check each cell in the range against the TODAY() function. Please assist. Thank you!

    1. Aaron:
      Here's the formula to accomplish what you want.
      =COUNTIF(J3:J11,">"&TODAY())

      1. Aaron:
        You could get a little fancier with:
        ="Dates Greater Than Today"&" "&"="&" "&COUNTIF(J3:J11,">"&TODAY())

        1. Aaron:
          Or finally you could put the TODAY() function in another cell and reference that cell in the formula like this:
          ="Dates Greater Than Today"&" "&"="&" "&COUNTIF(J3:J11,">"&K3)

  5. I have a problem...
    1,2,2,2,3,3,6,6,7,8,9= 7 .how to count without repeating the numbers. I mean I must count repeated value only once . I have to get result as 7 , how??

  6. i want to get formula which count same text in a list and decleared it as "good",or "bad"

  7. Hi,

    I'm currently working on trying to find out how many of my students enrolled in week 1 of my course completed and passed my class.

    I am using =countifs(class start date range, ">="&A1, class start date range, "<+"&B2, Student Status range, "completed class and should advance") I get 0 when I have 2 in my sample data that started the course within the date range and advanced.

    What am I doing wrong?

    Thanks in advance for your help!

  8. Hidears
    I have put numbers in A1 and A2 cell and put space in A3 cell and in A4 cell put, =countblank(A1:A3), it returned the value zero and later in A5 cell used =clean(A3) and why not the value in A4 cell showing one!? please help, just started learning excel..

  9. I have a large spreadsheet which tracks my daily food and fluid intake.

    I have a two columns that contain the beverage and the amount consumed (see attached)

    0.4 Beverage - Coffee - Flat White with Skim Milk - Large - Coffee Shop
    0.3 Beverage - Coffee - Flat White with Skim Milk - Medium - Coffee Shop
    0.2 Beverage - Coffee - Flat White with Skim Milk - Small - Coffee Shop
    0.5 Beverage - Soft Drink - Coke - No Sugar
    0.5 Beverage - Soft Drink - Pepsi Max - No Sugar
    0.3 Beverage - Vegetable Juice - Low Sodium - V8
    0.3 Beverage - Coffee - Flat White with Skim Milk - Medium - Coffee Shop
    0.4 Beverage - Soft Drink - Pepsi Max - No Sugar - Vanilla
    0.3 Beverage - Coffee - Flat White with Skim Milk - Medium - Coffee Shop

    I want to count the number of times each similar beverage occurs each day, multiply it by the amount consumed to obtain a total for each beverage each day.
    I have tried countifs, countif, vlookup, match and index and array functions but I cannot seem to get the logic right.

    Help me Obi Wan you're my last hope!! :-)

  10. I was trying for an option to count sequence, including duplicates in date from current to earliest.
    Where seq on C when contains 2 or above , the date sequence to be found.

    PRIMARY DATA UNIQUE_PRO_PUR SEQ ON C SEQUENCE ON B
    APPLE 1_1 04/11/2017 0 1
    ORANGE 1_55 04/02/2017 2 2
    GRAPES 1_57 04/11/2017 0 1
    ORANGE 1_55 04/03/2018 1 2

  11. Hello! I'm trying to get my spreadsheet to add the following up:

    August 2018 Census Regular Agencies/ Third Party TOTAL
    Work Related Incident / Accidents

    Lacerated Wounds 2 0 2
    Incision Wound 0 0 0

    1. Had to resend bec comp is not cooperating.

      Hello! I'm trying to get my spreadsheet to add the following up:

      August 2018 Census Regular Agencies/ Third Party TOTAL
      Work Related...

      Lacerated Wounds 2 0 2
      Incision Wound 0 0 0

      Regular should read '1' instead of '2'; I wanted it to count an entry for the month of August if the employee is "Regular" or "Probationary", and if it was the same category as on the leftmost column.

      My formula is: =SUM(COUNTIF('WORK ACCIDENT LOG'!A:A,(MONTH(8))),(COUNTIF('WORK ACCIDENT LOG'!G:G,{"Regular","Probationary"})),(COUNTIF('WORK ACCIDENT LOG'!I:I,AUGUST!W51)))

      I've been trying Sumproduct, max(sum. Doesn't really help. Would appreciate any advice. Thank you!

  12. I want add "OR" condition between 2 countifs

    countifs($A:$A,">1",$A:$A,"80",$B:$B,"<100")

    How can i acheive this. Please help.

  13. Hi,

    I am currently using a timesheet to see how much of my day is spent doing certain tasks. It looks something like this:

    14:43(start) to 15:24 (finish) ADMIN
    15:24 to 16:59 Payment Recons

    I now need to use countifs/sumifs to see how much time I spent doing admin. I'm sure I can use the same formula to see time spent doing admin in a day or in a month, etc.

    Thanks so much!

  14. I need a formula to count and generate a new column with the data.
    If I have a column with repeat numbers:
    123
    123
    456
    789
    789
    New column is:
    1 of 2
    2 of 2
    or
    1-2
    2-2
    Thank you in advance,
    Miguel

  15. My issue is that I have 2 columns with date ranges (a start date and an end date.)
    These can span multi-month and in a few instances multi-year.
    I'm trying to create a formula that would determine how many instances of each of these columns land in January of a specific year (or February,etc. Trying to track how many of these fall into a month.)

    Thanks for your time!

    1. Stu:
      The above article explains this and more. I recommend you read it if you want to understand more about Excel. However, here's a synopsis.
      Where the dates are in the range F64:F76 and you want to count all the August 5, 2018 dates use this formula:
      =COUNTIF(F64:F76,"8/5/18")
      Same range and you want all the dates from August
      =SUMPRODUCT(1*(MONTH(F64:F76)=8))
      Same range and you want all the dates from August 2018
      and your Excel version supports COUNTIFS:
      =COUNTIFS(F64:F76,">8/1/18",F64:F76,"<8/31/18")

  16. Hi

    I am trying to do the following.
    Example:
    Row 4 - 80, 79, 56, BLANK
    Row 5 - BlANK, 34, 56, 88

    I want to be able to count the range in row 4, however, is a cell is blank then use the corresponding cell on row 5.

    The answer would be = 303 for the above.

    HELP PLEASE

    Andy

  17. I need help. I am trying to take column B with 9 different arrays such as Air Refueling WG, Mission Support Group, Maintenance Group, etc... Then in Column G I have three arrays.. Current, Overdue and NPNP.. what I am trying to do is break down the 900 cells to show me who is current and overdue per each separate array in Column B.

    For Instance: Column b has 9 Air Refueling WG cells and 7 are current and 2 are overdue. So I need a formula that will pick out the Air Refueling WG people and show me how many are current and how many are overdue. and then do that for each separate group in column b.

    Can this be done??

  18. Hello
    I am working on sheet to get customer attendance that how many days customer come into company sales register. specimen data is as follows;

    Cloumns A B C
    Customer Date Attendance(Result Col)
    Customer A 6/1/18 Need Count Result like 1
    Customer A 6/1/18 Need Count Result like 1
    Customer B 6/1/18 Need Count Result like 1
    Customer A 6/2/18 Need Count Result like 2
    Customer A 6/2/18 Need Count Result like 2
    Customer C 6/2/18 Need Count Result like 1
    Customer B 6/3/18 Need Count Result like 2
    Customer A 6/3/18 Need Count Result like 3
    Customer A 6/3/18 Need Count Result like 3
    Thanks & Regards
    Mirza Farooq

    1. how to upload excel file?

  19. i would like to write a formula in excel where eg. each time a person clocks in after 8:30 am then to time is accumulated for the month. eg. say the clocks in 8:45am then is shows time loss as 45 minutes

  20. Hello,

    I nedd one help to you.

    can you please provide me advice.

    I have vendor & payment tracking excel sheet.

    I entered the sales details in vendor sheet.

    we are selling the materials to lot of vendors.

    coming to the point.

    Vendor sheet i maintaing Vendor name, Invoice date, actual value, GST and net value.

    Payment sheet i entered only Vendor name and Invoice date only.

    Both sheet compare and coming to the total value.

    is it possible to excel.

    Please give me the formula.

    Regards,
    Vijay

  21. I need a program that can generate for me a criteria based on three issues in rows a,b and c for 20 categories as columns such that cell will have 3 scenarios in each column and row produced to the 20 columns.how many cells contents will i have in total 20x3x3? or 20x3x3x3? which branch of maths is this? can it be done in excel? if not which program?
    kind regards
    Geoffrey

  22. I need a program that can generate for me a criteria based on three issues in rows a,b and c for 20 categories as columns such that cell a will have 3 scenarios in each column and row produced to the 20 columns.how many cells contents will i have in total 20x3x3? or 20x3x3x3?
    which branch of maths is this?

  23. Hi,

    I have a survey with the following columns: User, Role, Q1 (Like, Dislike, Neutral), Q2 (Like, Disklike Neutral).

    I have my 'countifs' working with named ranges (Role, Q1, Q2), such that I have

    =COUNTIFS(Role, "Accounting", Q1, "Like")
    =COUNTIFS(Role, "Accounting", Q1, "Neutral")
    ...
    ...
    =COUNTIFS(Role, "Payroll", Q1, "Like")
    =COUNTIFS(Role, "Payroll", Q1, "Neutral")
    ...
    ...
    =COUNTIFS (Role, "Accounting", Q2, "Like")
    etc etc
    etc etc

    This works, but it is time consuming to manually input the roles and response as the title for each line, and then update the formula to have the role/response each time.

    I'm wondering if there is a way to kind of combine the 'subtotal', or somehow 'group' this information, or use this formula differently so that I can more easily get the counts without having to define the role manually each time ex. like it would just see 'at each change in Role, use the countif"

    So the output would be:
    Accounting Like - 10
    Accounting Dislike - 20
    Payroll Like - 15
    Payroll Dislike - 5

    Hope my questions is clear enough!

  24. I am trying to plot a formula to count month names (i.e. Jan, Feb, Mar, Apr)and have prepared a drop down list on cell Q16 with month names. I want that if I select a month it gives me the count of that month only, other wise if I select All it would give me the count of all months. Please help

    =COUNTIFS(Sheet1!R:R,Q16,Sheet1!R:R,"Q16")

  25. I'm close to understanding by reading all of the above, but I have a question. I need to compare two columns, counting the number of times a name in column A matches a name in column B. Column A being the constant unchanging column. There are MANY names so spelling them all out in "" is not efficient, I would love to cell reference them if possible. Thank you!

    1. for example...count how many times the names from B match A:
      A B
      John Smith James Doe
      Jane Smith Eric Li
      James Doe George James
      Jana Doe Mary Patt
      Eric Li Tom Wright
      Hilda Smyth Mike Smith
      George James Ryan Garrett
      Ryan Garrett Greg Joseph

      1. Heather:
        If you want the number of times the names are duplicated I think this will work:
        =SUMPRODUCT(COUNTIF($A$2:$A$10,$B2:$B10))
        Where the data is in columns A and B cells 2 to 10.

  26. What is the formula to add alternate cells if we want to add cells like a1, c1, e1,.....more than 50cells

    1. Sunder:
      The formula to add cells in every other row is an array formula so after you enter it into the cell where you want the result to show be sure to select the formula in the formula bar and press CTRL+SHIFT+ENTER. When you do you'll see the curly brackets around the formula indicating Excel recognizes it as an array.
      You can change the $A$1:$A$100 or $A$1:$B$100 to the range you need.
      Formula to add every other row in column A is:
      =SUMPRODUCT((MOD(ROW($A$1:$A$100),2)=MOD(ROW($A$100)-1,2))*($A$1:$A$100))
      Formula to add every other row in column A and B is:
      =SUMPRODUCT((MOD(ROW($A$1:$B$100),2)=MOD(ROW($A$100)-1,2))*($A$1:$B$100))

  27. i have to extract multiple records from the large range of data with one criteria, i have tried with various formulas, could you please support us

    Make Customer Name Status Quote No/PO No Date Part No
    TVT L & T Kobelco Open 36 14-05-2018 BSF2250-55
    TVT L & T Kobelco Open 36 14-05-2018 BSF2650-70
    TVT L & T Kobelco Open 36 14-05-2018 BSF3666-70
    TVT L & T Kobelco Open 36 14-05-2018 BSF4276-55
    TVT L & T Kobelco Open 36 14-05-2018 BSF56100-80
    BPK Flowserve MM Closed 423974 11-05-2018 B7M-180240439
    BPK Flowserve MM Open 423974 12-05-2018 P21i-190M
    BPK L & T Kobelco Closed 3100001603 12-05-2018 484C-520
    BPK L & T Kobelco Closed 3100001603 08-05-2018 483C-450
    BPK L & T Kobelco Closed 3100001603 08-05-2018 483C-430
    BPK L & T Kobelco Closed 3100001603 08-05-2018 483C-390
    TVT Maha Hydraulics Open 33 10-05-2018 JAWS - VICE KURT
    TOOLFLO Avalon Tech Open 32 08-05-2018 MTECS 06032C12 32UN AC22R
    TOOLFLO Sundaram Clayton Open 31 08-05-2018 MTEC 0606C9 27NPTF AC22R
    TOOLFLO Sundaram Clayton Open 31 08-05-2018 MTECZ 08076C10 27NPTF AC22R
    KURT Sai Engineering Open 30 07-05-2018 KURT DX6
    Haimer Auto Bhan Closed 29 07-05-2018 40.521.16
    Haimer Auto Bhan Closed 29 07-05-2018 81.160.06
    TOOLFLO Q2Q Solutions Open 28 07-05-2018 MTECZ 1009C28 1.75ISOAC22R
    TOOLFLO Q2Q Solutions Open 28 07-05-2018 MTEC 0808C28 1.75ISO AC22R
    TOOLFLO Flowserve MM Open 25 03-05-2018 LN25DIR8UN AH725
    Haimer Reep Industries Open 23 03-05-2018 F1004NNL0300CAA

    i need to extract report based on Make or Customer

    1. Ramakrishnan:
      Does one report need to contain Make or Customer?
      Do you need one report to contain Make and another report to contain Customer?
      Does your Excel version support creating tables?
      What's the number of rows you need to search at one time?

  28. Range of cells Sheet1 G5 to G3208 if the condition "B4" is met in one or more of these cells I need the Sheet1 O5 to O3208 checked for a condition of "reject" on the same ROW of any "B4" conditions and the sum placed at Sheet2 D4 can anyone help me out with this ?

  29. Good afternoon

    Please assist. How do i count number of times data repeated itself in a row and in a column.

    Thank you

  31. I have a small query to you but big for me. actually i have a data of whole month. I need the count of failed item which are continues failed more than 3 times. for any example 1may to 3 may Failed then i will get count 1, or if first 2 days failed but on 3rd day got success count will be 0. Like that might be in the mid of month same kind of situation will raise. So it will sum count.

    Much appreciate if you can help me.

  32. Could I use COUNTIFS to determine how many entries in a multiple-column range match a date in another column?
    eg.
    A4:B35 against dates in N4:N35

    If not what would be the alterntive?

    I need to count how many payments were made in April and how many were made in May.

  33. please share how to use formula on this condition =COUNTIF(AND

  34. COUNTIFS does not work as you have presented here.
    I have two sets of variables. I want to count (for instance) how many of them have the string "njem." in A:A and the number 1800 in D:D.
    The solution suggested here is wrong. It gives me an error message.
    COUNTIFS seem to need semicolons separating ranges and criteria, not commas. And even with semicolons it doesn't work. It gives me a "#NAME?" message.
    Are we talking about the same programme here?

  35. Hi - I have looked at all the info above but I am still having problems. I need to work out the number of times something occurs

    I have one column that has dates that range from 01/08/2017 to 31/07/2020

    I have a second column that has numbers 1, 2 or 3

    I want to work out how many times the number 3 occurs between the date range 01/08/2018 and 31/07/2019

    Can you help ..... thank you

  36. Hi,

    I have an excel sheet with Column A = Due Date and column B = Delivery Date. I want to count how many times the Delivery date is before Due Date. Is this formula correct?

    =COUNTIFS(B2:B20,"<="&A2:A20)

    Doesn't seem to work.

    Thanks.

  37. Hi,

    I have excel sheet containing following columns:

    1. Bill Date 2. Bill Amount 3. Clients Name 4. Sales Team

    Now question is that we want a total bill amount of client name and sales team name wise.
    can you help us.

    1. Using the data, I would first create a table. Then using this table, I would create a pivot table. Using a table as the data source for your pivot table has definite advantages. For example, each time you add data to the table and then refresh the pivot table, the results using the new data is shown in your pivot table. As your data changes you can dynamically view your data. If pivot tables are new to you, search the web for "Use a table for your pivot table".

  38. I need a formula to add total points score for individual sales people:

    sales points
    CC 1
    AB 3
    AB 3
    AB 1
    AW 1
    AW 1
    JL 3
    AB 1
    AB 1
    AB 3
    AB 1
    AB 1
    AW 1
    AW 1
    AW 1
    AW 1
    AB 1
    AW 1
    AW 1
    AW 1
    AW 1
    JL 1
    JL 3

    How can I do this I have tried a countif formula, but doesn't seem to like it.

    Thanks

  39. My Formula2 {=SUM(COUNTIFS(C5:L5;$O$5:$O$8))} worked within minutes. Exactly what I needed. 06 april 2018, Hoogeveen,NL

  40. Hello,

    I would like that Excel counts the number of times it finds a combination of several criteria of the same column.

    Let me explain:

    For example, I want Excel to search in column A to find the words "apple","orange" and "banana" if in column B, the cells for apple, orange and banana contain the word "ripe". So basically, I want to say "Excel please go to column A and look for the word "apple" if the corresponding column B has the word "ripe". Then, go back to Column A and if you also see the words "orange" and " banana" with the word " ripe" in Column B, count that as "1". Therefore, apple = ripe, orange = ripe and banana = ripe would be counted as 1. If it has just orange = ripe and banana = ripe, it would be counted as zero (0).

    When I use the function:=SUM(COUNTIFS($C$2:$C$11, {"cancelled", "pending", "in transit"})) as in the example above, it counts each entry ("cancelled", "pending", "in transit") as a single unit instead of as one unit containing the three criteria.

    I hope you understand what I am asking.

    Thanks in advance.

  41. Budget vs achievement the incentive will be, if 111% is 7000

    please somebody can help me to writ the formula

  42. Hi,

    I am counting from a random list but when the formula reaches a specific amount I want it to stop. Say I have 6 items but I want the cell to only count 3. How to resolve?

    =if(COUNTIFS('SF1 Accident Log'!C12:C1000,ʺAvatarEvilʺ,'SF1 Accident Log'!D12:D1000,ʺReprimandʺ) >=4,COUNTIFS('SF1 Accident Log'!C12:C1000,ʺAvatarEvilʺ,'SF1 Accident Log'!D12:D1000,ʺReprimandʺ) -3 , ʺʺ)

  43. Hi
    I want to count cells based on two or more criteria_range / criteria pairs. As shown in your example to get the number of "Apples" orders that are "Cancelled" or "Pending" or "In transit".

    I copied the data and formula (with correct capitalisation). I get the answer "1" and not "3". The countifs is looking at the first field in the {}. I found this out by altering the order of the data in the {}. This is a problem I was having with my own data and why I am googling the problem. Can you confirm that you still get 3 with the latest version of google sheets.

    1. Apologies this is for excel and not google sheets. Ignore.

  44. how can I count number people with blank columns, e.g. person x in column c has blank cells in column E. I want to count that person has blank cells.
    Thanks

  45. I need a formula where it will count the number of dates associated to a company but will only count a date once. For example if a company has 4/8/16,9/8/16,4/8/16,12/16/16, it return 3 for the count and not 4 since 4/8/16 appears twice.

    Thank you

  46. I love this site. I always find what I need here. Thank you!

  47. I'm looking for a formula to count all of the information in two separate columns. I've tried everything, but it's not working. My last attempt is: =COUNTIFS(B59:B81, ">=a") + (F59:F81, ">=a")

  48. Hello
    I'm trying to figure out a formula that will compare pricing contained in 3 different columns and over 1000 rows, against pricing from 1 column and the same number of rows but then show per cell whether it's value is greater than or less than its respected compared price. Example: in column "B", "C" and "D" there are prices occupying hundreds of rows for those columns. They are to be compared to the prices listed in column "A" for that same row, but to return the values in a "green" or "red" value to signify if it is greater than or less than column "A"s price.

    Any ideas?

  49. bb1 = countif($A$1:$B$1,"=2",$A$1:$B$1,"=3") bb2= countif($A$1:$B$1,"=2",$A$1:$B$1,"=4") please tell me how to make this for 20 times . Thank you.

  50. Good afternoon all:

    What I would like to do is compare to sets of dates. Column C has the date of when a file was turned in say "1/1/18". Column A has the date of when a file was move into say "12/15/17". I want to see if the difference between the two is more than 16 and how many times that happens. Not sure if this is possible just thought I'd as. Tried the CountIf function but can't figure out the correct criteria.

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