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

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

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

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

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

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

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

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

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

  9. Good afternoon

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

    Thank you

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

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

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

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

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

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

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

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

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

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

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

    please somebody can help me to writ the formula

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  43. Data sheet below,
    A B
    1 Student Name Date
    2 Mr. X 11/18/2017
    3 Mr. Y 11/18/2017
    4 Mr. X 11/20/2017
    5 Mr. X 11/25/2017
    6 Mr. X 12/21/2017

    Above are the details of the persons visiting our office, i want to know, how many times the person Mr. X has visited our office in November only.

    Where as the result should be = 3

    I am using the below formula...

    =COUNTIFS(A2:A6,"Mr. X",B2:B6,">="&DATE(2017,11,1),B2:B6,"<="&DATE(2017,11,31))

    But i am getting the result = #VALUE!

    Can you please help ......

    1. Have you solved your problem?
      I have the same issue and i'd like to know the solution for this error "#VALUE!".
      Thanks.

    2. Hello,

      Actually, your formula is correct. If it doesn’t work, then most likely something is wrong with your table.
      If you can send me the workbook with your data and formula to support@ablebits.com, I’ll be able to help you better. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Don't forget to include the link to this comment into your email.

      I'll look into your issue and try to help.

  44. I am trying to add the number of Job Titles (Column C) and their Status - Open, Filled (Column B).

    I have tried Count if and subtracting the data but it doesn't seem to be working.

    =COUNTIF(C11:C67,"CSR",+ B11:B60,"O")
    How can I get the number of CSRs that are open between Column C and B?

    1. Hello, Jaime Manale,

      Please try the following formula:

      =COUNTIFS(C11:C67,"CSR",B11:B67,"O")

      Hope it will help you.

  45. I have a formula which is like this

    =COUNTIFS('sheet1 '!W:W,">0",'sheet1 '!A:A,"Truck")

    which is count all data in column W (sheet1) larger than 0, which are called Truck in column A.

    Now, I want this formula to read the same information but by consider a range of dates in between 1st of October to 15th (I have dates in Column B in sheet1).

    I now, may I can add to countifs, But not sure about it.

  46. I have one file, it has 3 systems associated to it so it is listed 3 times. Once I have sent all 3 systems for comments I want it to count as 1 as the 1 file has been sent for comments and is complete. So I have currently 1 merged cell for the file and 3 single cells for the systems but I would like it to know when all systems are sent for comments so that I don't need a merged cell, it just counts as 1 once all systems for that filename are sent for comments...not so easy I know but maybe someone can help!

  47. How do I create a formula for the criteria range in a countifs function?

  48. Hello Svetlana
    I have 2 columns, 1st contains date and 2nd condition,
    I want to count the number of cells contains date range 01-01-2015 to 31-12-2015 with condition A, please help.....

  49. Can the countifs formula count between a date rage with two additional criteria? For example: I have a table that list product by type (industrial, apparel, ect.) in column A, the status (completed, late, on hold, ect.) in column B, and the date entered. I want to be able to count how many apparel products had the late status in June.

  50. Hi

    I have a table which shows the gradings for each time estates are inspected ; 'Gold', 'Silver', or 'Bronze'.

    I want to know how to count many time the estate rating (Column C) changes from Gold, to Silver or Bronze for each block (column A) on So, for example, on row 9 the estate rating for Ian Mikardo Way, Amersham Road RG4, inspected on 26/06/17, changed from Gold to Silver compared to the previous inspection on 13/04/17.

    Thanks for any help you can give

    Block (A) Inspection Date(b) Estate Rating (C)

    Ian Mikardo Way,
    Amersham Road RG4 13/04/2017 Gold

    Ian Mikardo Way,
    Amersham Road RG4 26/06/2017 Silver

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