Comments on: COUNTIF function in Excel - count if not blank, greater than, duplicate or unique

The article explains Excel COUNTIF function, provides a number of examples and warns about possible quirks when using COUNTIF with multiple criteria and specific types of cells. Continue reading →

Comments page 25. Total comments: 1074

  1. Hi Svetlana,
    Pls help me
    I want to count "o" in J2:PB2 when E2 is greater than 8 (E2>8) .

    Thank u :)

  2. hey there, i wanted to sum or count a list, excluding some specific names. Like there is a fruit list and wanted to count/sum excluding apples and mangoes.

    i have five brands in the sales list A/B/C/D/E, want to sum only A/D/E

  3. hey there, i wanted to sum or count a list, excluding some specific names. Like there is a fruit list and wanted to count/sum excluding apples and mangoes.

  4. Hello please am trying to play around to see if i can use COUNIF or COUNTIFS to make my formula to count P and PS as one and the same thing in my totals. Thank you so much for the rapid response

    1. Hi Silvester,

      You can add up 2 COUNTIF functions, like this:

      =COUNTIF(range, "P") + COUNTIF(range, "PS")

  5. =COUNTIF(I:I,""&"")
    Column I contains formulas that return "", or a message, the message may vary. I am trying to count the messages, but the count returned is of cells containing a formula.

    e.g. the formula ="" put in a cell will increment the count

    This is in Excel 2013

  6. Nice article, thanks!
    I have two columns of data like below. I need to count the number of times the value in column A is greater than the one in column B. Is this possible using COUNTIF?

    A | B
    2 | 1
    1 | 2
    3 | 0
    2 | 1

    Result A > B: 3 times
    Result B > A: 1 time

      1. Thanks, that works great! This is the formula I’m using (in Google Sheets):
        =ArrayFormula(SUM((Data!B2:B1000 > Data!C2:C1000) * 1))

  7. Hi need to calculate how many times for particular month my in time is 9:30, 9:40, 8:20,10:20,9:50,..........in month end 9:50. Please suggest formula for how
    Many days I have come late my office time is 9:00.

  8. Hi Svetlana,

    I have a table that looks something like this: (0 is blank)

    A1 B1
    high run
    0 run
    0 run
    high run
    0 run
    0 run
    0 run
    0 run
    0 run
    high run

    I want to count the number of "run"s that occurs in the cell +1 column and -1 row of the cell with the value "high". So in this example, the return value would be 2.

    I am only able to count for one instance rather than the whole column. Can you please give any insights on writing the formula for this problem?

    Thank you

  9. Hi Svetlana,

    I have a table that looks something like this:

    high run
    run
    run
    high run
    run
    run
    run
    run
    run
    high run

    I want to count the number of "run"s that occurs in the cell +1 column and -1 row of the cell with the value "high". So in this example, the return value would be 2.

    I am only able to count for one instance rather than the whole column. Can you please give any insights on writing the formula for this problem?

    Thank you

  10. I've got a table of data that I have then created a number of concatenated columns from. From example, in my data, I have lists of contract numbers, and the month in which those contracts were processed (in some cases, this is across more than one month). I have therefore added a concatenated column that combines the contract number and the month number (1 for January, etc).

    I want to be able to find out the number of times each contract has occurred within each month so I have tried a simple countif formula that says =COUNTIF(A:A,C1). Column A contains the concatenated column and C1 is where I have the contract number and month number also concatenated for the countif to be based on. However, I get a zero every time even though I know that combination of contract and month number appear three times.

    Is my problem because all parts of the formula are referring to concatanated fields?

    Thanks for any help provided - it should be so simple but there must be some sort of limitation in Excel that is holding me back.

    1. I should add that the maximum length of the concatenated contract/month field is 10 digits.

  11. Hello;
    I'm looking to set up a formula that will count all values in column A, when column B has a date of 2012?
    Thank you!

    1. Hi Grace,

      If you want to sum the values in column A, then you can use a formula similar to this:
      =SUMIFS(A1:A6, B1:B6, ">=1/1/2012", B1:B6,"<=12/31/2012")

  12. Hey, i need your help for

    Col 1 has Unique id ( Emp Code) and col 5 has date.

    i need to find out whether a Emp Code has Visited for more then once in a days.

    exp emp code 101 and Emp has Visited for more then twice a days in 2 place.

    need to make report regarding Emp Visited on tht day

  13. Please help me. I'm stuck on the following excel formula:

    =IF($H$26="Acting","","",IF($H$26="Temporary","","",IF($H$26="PERMANENT"(VLOOKUP($D$20,'Job Title'!$A$2:$C$28,3,FALSE),""))

    “Acting”, “Temporary”, or “Permanent” are text
    If H26 is “Acting” or “Temporary” return should be blank
    If H26 is “Permanent” it should perform a VLookUP at different D20 and compare it to spreadsheet named Job Title.

    Getting error: "The formula you typed contains an error"

  14. Hello,

    I have a simple two column worksheet. Column one consists of names, and column two of dates those names were given a task. I need a total that calculates how many times each name was given a task on today's date.

    So each day, as the tasks are assigned and entered the total should reflect how many were given to each name that day.

    I am trying to use a countifs formula. I have B:B as the Criteria_range1, and =COUNTIF(B:B,TODAY()) as Criteria1, but this doesn't work.

    Can somebody tell me what I need to enter to make this calculation work? Any help would be greatly appreciated.

    1. Hi Douglas,

      The formula is correct and it worked perfectly on my test sheet. I can think of only reason for it not working - dates formatted as text. Is it the case?

      1. I currently have column B formatted as Date, and chose the 03/14/01 format. :(

        1. Thanks for the help Svetlana. I'm sure it is something minor I am overlooking, but I can't figure it out. I have tried formatting column B as text, date and custom but still cannot get it to work.

          1. Douglas,

            Minor things often cause big problems :) If you can send your sample workbook with the formula to our support team (support@ablebits.com) we will try to figure it out.

            1. Svetlana, thanks, that did the trick!!!! You're amazing, and this site is wonderful! Thanks again!!

  15. Nice Article!

  16. I am trying to count occurrences above a certain number; however, those values I want to count are not in a straight row or column. I have each company, then label, then each month in the columns. For each company in the column, I have 4 sets of information. I want to count 1 set for each company. How do I do that?
    Sample:
    Company A $ of x $50
    $ of y $12
    # of employees 49
    Company B $ of x $150
    $ of y $75
    # of employees 289

    For each company, I want a count of any time the number of employees in that column is greater than 250.

  17. Hello,

    I am trying write a function to count the number of unique values in one column that fall in a category in another column.

    Here is a simplified example of my data:

    Order Qty Group
    1 1 1
    2 2 1
    3 1 1
    4 4 1
    5 5 2
    6 8 2
    7 2 2
    8 4 2
    9 6 2

    I want to know how many different/unique order quantities there are within group 1, group 2, etc but without designating the cell range, just the particular value (1 or 2 in this case).

    Thanks so much!

  18. ________________
    |__a_|____b_____|
    |poo |480 menit |
    |ssp |360 menit |
    |LLLL|300 menit |
    |LLLL|300 menit |
    |____|__________|

    so the the result would be like this :

    ________________
    |__a_|____b_____|
    |poo |480 menit |
    |ssp |360 menit |
    |LLLL|600 menit |
    |____|__________|

    how to do opperation like that ? thanks before

  19. Hi Svetlana,

    I have a problem I think can be solved using this method but I cant seem to get the syntax right

    I have a list of words in 1 column that have duplicates and a date range in another column

    What I need to do is count non duplicates when the date is less then 7 days ago.

    the table would look something like this

    Apple 26/6/2015
    Apple 24/6/2015
    Pear 20/6/2015
    Pear 15/6/2015
    Pear 22/6/2015
    Orange27/6/2015

  20. Hi Svetlana,

    I am using excel 2003 :(

    I am trying to count how many cells in column D contain a specific text, IF the dates in column F are greater than 6 months ago from today.

    I have tried this:

    =SUMPRODUCT((D4:D16390=D4)*(F4:F16390="<="&TODAY()-180))

    But cant seem to get it to work, just shows 0.

    Many thanks!

    1. Hi Gary,

      Try the following array formula:
      =SUM((D4:D16390=D4)*(F4:F16390<=TODAY()-180))

      Remember to press Ctrl+Shift+Enter to complete it.

      1. Hi Svetlana,

        Perfect! Thank you very much for that!

  21. Hi,
    I need to count the amount of profit-making enterprises:

    Проект* Выручка Себестоимость
    SAMA-001_ALFA 100 90
    SAMA-003_BETA 120 200
    ...

    THANK YOU

    1. Count #projects if = Выручка>Сеьестоимость

      1. Hi,

        Supposing that "Выручка" is column B and "Себестоимость" is column C, you can use the following array formula:

        =SUM(--(B2:B10>C2:C10))

        Remember to press Ctrl + Shift + Enter to enter the array formula correctly.

  22. Can you please give me solution for my earlier email?

  23. Please help me to solve my assignment I dont know how...
    Count if
    A B C
    1. 10 20 15
    2. 5 25 4
    3. 3 6 2

    Count if
    1) 5-2
    2) 1-5
    3) 15-25

    1. Hello Nikki,

      Here you go:

      1) 5-2 =COUNTIFS($A$1:$C$3, "<="&5, $A$1:$C$3, ">="&2)

      1) 1-5 =COUNTIFS($A$1:$C$3, "<="&5, $A$1:$C$3, ">="&1)

      1) 15-25 =COUNTIFS($A$1:$C$3, "<="&25, $A$1:$C$3, ">="&15)

  24. Hi Svetlana,

    I have some data like this in a column. If am giving formula (=COUNTIF(A1:A2000, A1)) to count the number of repetition of each number its not getting counted properly. Its taking into con sideration only few entries. May be coz character are more than 19. Is there any way to count the repetitions if the number of character in a cell is more than 19.

    89XXXXXXXXXXXXXXX44
    89XXXXXXXXXXXXXXX36
    89XXXXXXXXXXXXXXX28
    89XXXXXXXXXXXXXXX10
    89XXXXXXXXXXXXXXX02

  25. Hi Svetlana,

    Looking for an idea for a function. I'm pulling data from one spreadsheet with account numbers assigned to different agents, and trying to compare it to the number of entries made in our sales database for these accounts. The problem is, the database entries contain the account numbers, but also contain other text/numbers as well. Any suggestions? Thank you!

    1. After tinkering around with it, I think the main point of my problem is getting the *value* function to work with a cell reference as the value, rather than a constant.

  26. Hi,

    Can you help me out in finding the hours in an column G which is >4 <6 and =6.

    time is in 4:00:00 formate and also i need to find no 4 to 6 in same column

    Thanks
    Shwetha

    1. Hi Shwetha,

      You can use the TIME function in your criteria, like this:

      Times greater than 4: =COUNTIF(G2:G100, ">"&TIME(4,0,0))

      And use analogous formulas for other calculations.

  27. Hi,
    I know how to do this using PIVOT, but I have a non power user that will use a template sheet in excel and I'm trying to help him to get a count duplicates cases based on a condition.

    Case Cause Dealer
    447839 L CA
    447839 P CA
    447839 S CA
    448387 L CA
    448387 P CA
    447646 L PH
    447646 P PH
    447647 L PH
    447647 P PH
    447648 L PH
    447648 P PH

    In above example CA have 2 cases and PH have 3 cases. Using PIVOT is easy to get the result rather then calculating through transactional data but the user isn't PIVOT oriented and I tried hard to trained.

    How I can achieve above summary results using a function/complex formula.

    Anticipate thanks,

    Jose

  28. Hello,

    I have the following data:

    Column A Column B Column C
    Estimate Percentage Percentage Expected
    (days) Complete Remaining Result

    0 0 Not Required
    5 25 3.25
    1 75 .25
    Not Required 0 Not Required

    So, those fields that are not a number and are equal to zero should not be calculated, but for the formula used to calculate "Percentage Remaining", it is: Column A * (Column B /100)

    So, my question is: How do I exclude the calculations that are zeros and are not numbers in Column A and Column B?

  29. Hi, I have a spread sheet where I have a resident name in column A and a hall number in column B. There are multiple residents on the same hall, and sometimes the resident is listed more than once. I want to count the number of times a hall is listed, but no duplicates(i.e Joe 100
    Bill 200
    Joe 100
    Chris 300
    Maple 100. So I would want to find out how many times 100 is involved without the duplicate so the count should be 2. Can you help me? thanks,Joe

  30. Hi - I have a baseball schedule and trying to see how many times each team has a early and late game. I have used the countifs but can't get it to work.
    Away Team # Vs Home Team # Time and Diamond
    1 vs 2 715 P
    3 vs 4 845 P
    5 vs 6 715 D1
    7 vs 8 845 D2
    11 vs 12 715 D1
    9 vs 10 845 D2
    12 vs 1 715 P
    10 vs 3 845 P
    8 vs 5 715 D1
    6 vs 7 845 D2
    4 vs 9 715 D1
    2 vs 11 845 D2
    Thank you, Kendra

    1. Hi Kendra,

      And how do you determine whether it's an early and late game?

  31. Hi Miss Svetlana how i could count the total sum of all tools without using pivot example.
    Colum A. Colum B.
    Description: Total Amount:
    Tools & Equipment A 3,000.00
    Tools & Equipment B 4,000.00
    Tools & Equipment B 1,000,00
    Tools & Equipment C 2,500,00
    Tools & Equipment D 500,00
    Tools & Equipment C 1,500.00
    Tools & Equipment E 800.00
    Tools & Equipment A 3,800.00
    Tools & Equipment E 540.00
    Tools & Equipment E 900.00
    Tools & Equipment E 100.00
    Tools & Equipment E 100.00
    Tools & Equipment E 300.00
    Tools & Equipment B 6,000.00

    Can u help me pls.......

    1. i need to total by each tools without using pivot

  32. Svetlana,

    I'm trying to make a formula that does not count any cell that is Blank or 0. What am I missing here? "=-COUNTIF(B5:F5,B6:F6,"<=0"),(B5*B6+C5*C6+D5*D6+E5*E6*F5*F6)/G5" The B5..F6 cells could be blank or 0, but I just want a simple formula that does provides the weighted average.

    Thank You!

    Johan Bolle

  33. I have summation formulas in column Z and 2 cells with a value > 0
    When I use the COUNTIF(Z5:Z108,">0" ) it correctly answers 2
    How can I combine that with the question "How many are SHOWN that have a value > 0 somewhat like =Subtotal(109,Z5:Z108)

    Unsuccessfully tried
    SUBTOTAL(109,COUNTIF(Z5:Z108,">0")) and
    COUNTIF(SUBTOTAL(109,Z5:Z108),">0")

  34. Hi Svetlana,
    I am using COUNTIF to give me a count of results that are in a range. I want to know how many fall between 8,000 to 9,000, 9,000 to 10,000, and so on. My range of data to look at is A1-A164 but within that range are many subcategories with totals for that category. My COUNTIF counts the total in the results but I don't want the totals counted. Other than making 50 separate ranges is there any other way you can think of to exclude the total cells from being included in the count?

    Thank you for any help you can offer.

    1. Debbie,
      I realize it has been some time since if you inquired; my apologies. If I'm understanding your dilemma properly, is it not possible to use COUNTIFS and ignore rows where the total values have the keyword "Total" in an adjacent column?

    2. Hi Debbie,

      I am afraid I cannot figure out any way other than you suggested :(

  35. I am trying to keep track of attendance of employees for the current month. I have a formula that will calculate the last 30 days for the attendance code, but instead of last 30 days I would like for current month only:

    =COUNTIFS(Attendance!$B:$B,$B5,Attendance!$E:$E,"CIO - CI OUT",Attendance!$C:$C,">"&(TODAY()-30))

    I have 2 separate sheets - Attendance sheet which is the sheet I log attendance to, and the other sheet where I am placing my formulas on. Here is a breakdown of everything from that formula above

    Counts for that employee if it meets all of the following criteria:
    **Attendance!$B:$B,$B5 >> Employee Name (want to count specific employee)
    **Attendance!$E:$E,"CIO - CI OUT" >> Attendance code (want to count specific attendance code)
    **Attendance!$C:$C,">"&(TODAY()-30) >> THIS IS WHAT I NEED TO CHANGE. I need to change instead of being last 30 days, to be for the CURRENT month only.

    So today being in May, I want it to count how many times "John Doe" has had the Attendance code of "CIO - CI OUT" for THIS MONTH. It should reset next month to only include June automatically.

  36. Hi Svetlana,

    Can you help please?

    I am currently using the CountA function to count the cells in a column.
    Some cells have ? or * in them.
    I have the formula to not count these cells?
    How do I achieve this?

    Thanks much.

    1. Hi!

      You can count the cells with ? and * and then subtract them from the total count, like this:

      =COUNTA(A1:A100)-COUNTIF(A1:A100,"~*")-COUNTIF(A1:A100,"~?")

  37. Hi Svetlana,

    I have an excel table to keep a running balance of the decreasing loan amount, as payments are made (see excerpt from table below).
    Currently, the formula in column D is =SUM(D2,C3) & the formula in column E is =E2-D3
    The problem with this is that is when I continue the formula's all the way down to #21 on the list; the $150 in D continues all the was down the list, & the $4,350 in E does the same. It stays that way until I add a new payment to column C; and then the new total continues down the list.
    I don't want anything showing in Columns D & E until another payment is made. I want the formula's in there; so the balance automatically adjusts when I enter a payment, but I don't want any value showing if no payment has been made.
    I thought I could accomplish this by using a COUNTIF formula.
    Something like: If C is greater than zero, then =SUM(D3,C4)& =E3-D4.
    I'm not sure how to set the up.
    Can you help me with that? Any help would be very much appreciated.

    C D E
    $ AMOUNT of PMT BALANCE PD TO DATE BALANCE OWING
    $0.00 $0.00 $4,500.00
    $150.00 $150.00 $4,350.00

  38. Dear Sir/madam,

    Please help.

    I have a range of codes with some are duplicate in one column. I would like to count the code but for those are duplicate to be counted only one time.

    Veasna

  39. i want to know how calculate spreadsheets
    my no is this 109|110
    109|110
    109|110

    how many times 109 in this range

  40. I have a spreadsheets that tracks the schedule of my sales rep's from week to week. I have a column set up to count how many total appointments they have per day. In this column I use the formula =COUNTIF(tblMonday[@[9:00 AM]:[5:00 PM]],"*") so it reads all addresses as a count. I have another column set up to count only the warranty appointments for that day. In that column, I am using the formula =COUNTIF(tblMonday[@[9:00 AM]:[5:00 PM]],"(W)*") to recognize that when I type (W) in front of the address, it is a warranty call and will go just to that column. I would like to add a third column that counts only the addresses that have no (W) in front. Please... send help. :-)

  41. I have a table with numbers in Columns F, G, H, I, and J. There is an identifier in Column B.

    I need the average of all numbers in Columns F, G, H, I, and J where the identifier in Column B = A14 (i.e. the value in A14).

    I tried the following:

    =AVERAGEIFS('Hours-Backup'!F3:J1048576,'Hours-Backup'!B:B,'Group Dashboard'!A14)

    This results in a #VALUE! error.

    I can't figure out what the problem is. Any help would be appreciated.

    Ultimately, I want to make it so that the function ignores all cells that are non-numeric or blank. In other words, I don't want it to assume that blank cells are 0 for averaging purposes.

    Thanks!

  42. Hi,

    I was wondering if it is possible to count partial matches in the following example:
    Orange juice
    Apple juice
    Juice
    Coca Cola

    So would want the count if formula return 3. I believe if I use *juice it will count only the first two, as "Juice" doesn't have anything in front of it.

    Thanks!

    1. Hi Lidiya,

      You can use *Juice* and it will count all 3. The asterisk tells the formula to count all instances regardless of the presence or absence of any other characters in front of / after the word.

  43. Hello Svetlana,

    I need help with the following problem. I have a column with 4 different values:

    Column A
    39
    19
    20
    0
    20
    19
    19
    19
    0
    39
    19
    0
    39
    19
    39
    0
    0
    0
    19
    39
    20
    0
    20

    I need a formula that will count the number of appearances of the number 0 since the last time the number 39 has appeared. Numbers 19 and 20 are not important, but they have to be there. Basically it counts the number of times the lowest value in the column has appeared since the last appearance of the greatest value in the column. The formula will be put in column B and will have to show the following results:

    Column B

    0
    0
    0
    1
    1
    1
    1
    1
    2
    0
    0
    1
    0
    0
    0
    1
    2
    3
    3
    0
    0
    1
    1

    Is it possible?

    Thanks in advance!

  44. Hi Svetlana Cheusheva

    hope your doing well.. please help me out .. i have attached my worksheet with this mail.

    i want total value of cells A , B , & C to be calculated in Cell "D" automatically .

    please show me step by step..

    TERMINAL

    PRICE

    A

    1000

    B

    2000

    TICKET

    PRICE

    YES

    0

    NO

    150

    STAGGING

    PRICE

    STAGE-1

    510

    STAGE-2

    510

    PARKING

    0

    your support will be highly appreciated

  45. Hi Svetlana,

    I am trying to figure out how to formulate a countif function where I am trying to measure exposure on certain loans on any given date. I have a bunch of loans, the amount pertaining to that loan and the respective maturity/expiry date. Essentially, I am trying to measure peak exposure from today lookng forward over the next 5 years so that I can create a graph in excel illustrating exposure over time. I would like the graph to show me how much exposure I have to those loans in year 3 in quarter 4 and how many have been repaid.

    Do you have any idea how I might do so?

    Many thanks in advance,

    Ben

  46. Hey, great post, and thanks for the pointers! I have a basic timesheet for work and would like to count the days spent on each project. Are you able to tell me how I can redirect the condition to another cell? So that:

    =COUNTIF(timesheet!$C$4:$G$500,"project A")

    would become:

    =COUNTIF(timesheet!$C$4:$G$500,"cell reference that redirects to the beginning of the row, which is where the project is named")

    This way, I will be able to remove the need to enter the project name twice and the risk of an error due to billing days for project A against project B.

    Thanks!

  47. Hi,

    Is there any way we can figure out a formulae for this tracker. Basically tracker should automatically calculate the blood samples drawn 12 months from the day they signed the consent form.

    Eg- if patient signed consent in 27/07/2013 and the bloods are drawn until end of JULY 2014, the tracker should calculate signed consent + 365 days.

    Thanks a lot in advance.

    1. Hi Sat,

      I think it is possible. For me to be able to suggest a formula, please explain the structure of your data, i.e. what columns contain signed consent dates and blood drown dates and probably patients' names. Also, what exactly you want to get as a result - the expiry date for each blood sample or something different?

  48. Hi Svetlana,

    Thank you for publishing this page and always being active to respond.

    I am stuck with a Problem :

    Query : Count of projects between 01/01/2015 and 01/31/2015 whose status is ABC OR XYZ.

    From your forum I could do it for Count of project Less than 01/31/2015 but that will include all 2014 and 2013 projects also.
    I used the following formula for this :
    =COUNTIFS(Details!D17:D33,"<01/31/2015", Details!E17:E33, "ABC")+COUNTIFS(Details!D17:D33,"<12/31/2014", Details!E17:E33, "XYZ")

    Could you please help out with date range for this.

  49. Hi Svetlana,

    Thank you for this very helpful topic!

    I have this question: Is it possible with a formula to count all the rows from A to D in which the sum of the cells is greater than 0 and between 4.

    For example in this case, the result will be 2 :

    A B C D E
    1 0 1 5 0 4
    2 0 3 12 0 0
    3 0 1 2 0 1
    4 7 2 5 0 2
    5 0 0 0 1 4

    Thank you very much,

    Elena

    1. Hi Elena,

      Sorry, I am a bit confused. Do you mean the sum of values in columns A through D in each row, e.g. A1:D1, A2:D2, etc.? If so, it is always greater than 0 and even greater than 4 in your example. Please clarify.

  50. I want to know whether its possible to calculate and if yes what are the tags to find out whether the values have crossed the certain threshold and if it does can the names of whoever has crossed can be displayed

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