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 28. Total comments: 2074

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  17. Good afternoon

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

    Thank you

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

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

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

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

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

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

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

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

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

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

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

    please somebody can help me to writ the formula

  30. 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 , ʺʺ)

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

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

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

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

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

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

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

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

  39. I have been working on a sales report, that I am needing to get totals by date and name. I have been working on the formula and just can't figure it out. Sample below

    5/6/2015 Jason 200.00
    5/22/2016 Adam 400.00
    1/15/2018 Jason 800.00
    4/28/2016 Adam 500.00
    4/22/2018 Steve

    1. I have been working on a sales report, that I am needing to get totals by date and name. I have been working on the formula and just can't figure it out. Sample below
      Row/Col A B C
      1 5/6/2015 Jason 200.00
      2 5/22/2016 Adam 400.00
      3 1/15/2018 Jason 800.00
      4 4/28/2016 Adam 500.00
      5 4/22/2018 Steve 200.00

      I only want to see the total on sales from 2018 for Jason. How would I write the formula?

      This is how I tried, but it is not working
      =Countifs(a1:a5,">12/31/2017,b1:b5,"=Jason",Total,"c1:c5")

  40. hello
    i am trying to count full day and half day leave for team members

    B2 martin
    c2 Paid leave d2 1
    c3 paid leave d3 0.5

    i need a count of martin total leaves taken
    eg. Martin 1.5

  41. I am trying to count the number of cells that have a value greater than 100, which have a value of less than 20% in a different column. When I try to use this formula, I get a #Value result. What am I doing wrong?

    =COUNTIFS(B1:B726,"20%")

    1. You are so close, forgetting a minor key to major success. ' '

      Used int the formula, you'll need it as
      =Countif(B2:B726,"<20%")

      Stupid Note: I like to use it against decimal values.

      Hope you didn't need this in a rush.

  42. HI

    how can i Count word and multiple value in excel. for ed

    Data Sheet-

    Activity Date Topic Like Share View
    Facebook 01/01/18 Test 1 1 10
    Facebook 01/02/18 Test1 3 1 50
    LinkedIn 01/03/18 Test2 4 2 100
    Twitter 01/04/18 Test3 9 4 150

    Dashboard - Output required like

    Post Like - Jan - Feb - Mar - Apr
    Facebook
    LinkedIn
    Twitter

    ----------------------------------------------------------------------

    Need to count facebook like by the facebook posts.
    Can you please advise?

    - Philip

    1. Hello, Philip,

      If I understand your task correctly, please try the following formula:

      =SUMIF(A:A,"Facebook",D:D)

      Hope it will help you.

  43. Hi there, I am trying to total sales with 2 criteria.
    cell:A would have total products sold (1,2,3,4,5)
    Cell B will have date order submitted.
    I'm trying to add up sum of sales with 3,4, and 5 products sold today().
    I've tried a bunch of formulas and I either get #value or 0.

    1. I think I finally figured it out. I ended up using =today() in F12. So now it counts any orders in p29 through p70 that are greater than or equal to 3 and the date listed in u29 through u70 equals todays date.

      =COUNTIFS(P29:P70,">=3",U29:U70,F12)

  44. Hi,
    Am I able to refer to a cell with text to avoid typing the text? Instead of typing the name in the formula.

    Example formula in where F1 and G1 is referencing a cell with my criteria
    =COUNTIFS($C$2:$C$21,F1,$D$2:$D$21,G1)

    Thank you!!

  45. Hi,
    Am I able to refer to a cell with text to avoid typing the text? Instead of typing the name in the formula.

    Example formula in where F1 and G1 is referencing a cell with my criteria
    =COUNTIFS($C$2:$C$21,F1,$D$2:$D$21,G1)

  46. I am trying to do a formula that involves letters meaning amounts and adding the columns together. How would I do that?
    Name - D for diamond or $500, G for Gold or $100, S for Silver or $50.
    My columns look like this:
    Name 2018 2017 2016 2015 2014 Total
    Lesa Sandberg D G S S S $750
    I need to know how much money a person has donated over the past years and have it automatically total it. Is this possible? Thanks!

    1. Hello,

      Please try the following formula:

      =SUMPRODUCT(((B2:G2="D")*500)+((B2:G2="G")*100)+((B2:G2="S")*50))

      Hope it will help you.

  47. Seeking a formula:

    Currently use:
    =countifs((A1:A25),">="&DATE(2017,1,1),(A1:A25),"<="&DATE(2017,1,31))

    We now want to select

    Q1:Q25 if contains the letter "C" and also fits within the above date ranges...

    Please help

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =COUNTIFS((A1:A25),">="&DATE(2017,1,1),(A1:A25),"<="&DATE(2017,1,31),(Q1:Q25),"=C")

      Hope it will help you.

  48. Is there a COUNTIF formula that only counts 1 if the sum of two different cells totals a specific number? I've scoured the web and can't find an answer to this question.

    I don't mean the sum of either cells, but only if the two cells added together equal 1. If both cells have 1, I don't want it to count, because the sum of 1 + 1 = 2, and 2 is not 1. For some reason, can't find a formula to count only when ONE of the cells has a 1.

  49. Is there a COUNTIF formula that only counts 1 if the sum of two different cells totals a specific number? I've scoured the web and can't find an answer to this question. I don't mean the sum of either cells, but only if the two cells added together equal 1. If both cells have 1, I don't want it to count, because the sum of 1 + 1 = 2, and 2 is not 1. For some reason, can't find a formula to count only when ONE of the cells has a 1.

  50. How would you type a formula to find out if the sum of two cells is greater than or equal to 3? I tried entering COUNTIFS(A1+M1,"=<3") but was unsuccessful. I need to count multiple pairs of cells, not just one pair at a time.

    Thanks

    1. Hello,

      Please try the following formula:

      =SUMPRODUCT(((A1+M1)<=3)*1)

      Hope it will help you.

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