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

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

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

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

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

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

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

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

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

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

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

  11. Hi,

    I need to count the number of hours for midnight charge given the criteria to be 11pm to 6am.

    E.g.(1) Cell A1 9pm and cell B1 9am. the result for midnight hours should be 7hrs
    E.g.(2) Cell B1 12am and cell B2 10am. the result for midnight hours should be 6hrs.

    How should i input the formula?

    thank you very much.

  12. how to count a data by date but including text contain like

    A columns
    1-Mar-15
    6-Mar-15
    11-Mar-15
    16-Mar-15
    21-Mar-15
    26-Mar-15
    31-Mar-15
    a
    a

    how do i take all the count

    1. Hi Shoaib,

      Not sure if I understand the task correctly. Anyway, if you want to count all cells with any data in column A, you can use the following formula:
      =COUNTIF(A2:A100,"<>"&"")

      Please see the example in the "Count if blank or not blank" for full details.

  13. Hi
    I want to know whether it is possible to get the count of cells that are not empty.

  14. I am trying to count how may times Toronto plays a game in the U10 age group.

    Column A = Age group
    Column B = Home Team
    Column C = vs
    Column D = Away Team

    Age Group Team 1 Team 2
    U8 Moncton vs Halifax
    U9 Toronto vs Moncton
    U10 Moncton vs Halifax
    U12 Halifax vs Vancouver
    U8 Toronto vs Vancouver
    U9 Halifax vs Toronto
    U10 Toronto vs Vancouver
    U12 Vancouver vs Moncton
    U8 Vancouver vs Halifax
    U9 Toronto vs Moncton
    U10 Moncton vs Vancouver
    U12 Vancouver vs Moncton
    U8 Vancouver vs Toronto
    U9 Moncton vs Vancouver
    U10 Toronto vs Moncton
    U12 Halifax vs Fredericton

  15. if any cell in column b equals a certain text then count cells in column c

    post jan feb mar apr may
    admin 10 20
    admin 25 2
    asst manager 10 17
    asst manager 2 28
    bartender 21
    bartender 30
    captain 15 15
    captain 13 17
    hostess 10 15
    hostess 25 5
    manager 16 14
    manager 30
    manager 25 3
    manager 20 2
    supervisor 20 10
    supervisor 15 15
    waiter 25
    waiter 30
    waiter 20
    waiter 18
    waiter 10
    waiter 14 13
    waiter 20
    waiter 25
    waiter 28
    waiter 30
    waiter 15
    8 8 11 10 4

    admin 1
    asst manager 1
    bartender 1
    captain 0
    hostess 1
    manager 1
    supervisor 1
    waiter 2
    Total 8 0 0 0 0

  16. Hi,

    I want to format Column A of a sheet. Column A has names and in columns B:M are the 12 numbers (1-75) associated to each name. In Q1:Z30 I have up to (depending on the day) 300 random numbers 1-75. I need the name in Column A to turn Pink when 9 of the numbers match the numbers entered in Q1:Z30. Since there are up to 300 numbers entered in Q1:Z30, some numbers are entered more than once. Here is the Conditional Formatting formula that I am using that isn't working:

    =SUM(COUNTIF($B1:$M1,$Q$1:$Z$30))>=9

    The formula is counting each instance that it matches and adding those up, and when those 12 numbers have been matched 9 times it highlights the name in Column A. I need the formula to highlight when 9 of the numbers in B:M have been matched period, not how many times they have been matched.

    Thank you so much for your time!

  17. Hello,
    I need help with function Countif, so:
    I want to count two cells for example: if cell A1=1 or cell A2=1 so count as 1, if one of these cells equal 1, count as 1, if both cells equal 1, count 1 as well. How is it possible to do with function Countif or with other function?

    Thank you in advance

  18. I have two columns. I would like to count the the no. of values in the right column greater greater than the corresponding values in left column. How to accomplish this?

  19. Hi Svetlana - Hope you are fine, required small help from you, is there any formula to capture code from statment by using if formula. I have 34 Codes, need to capture below code if they come in different entries, Example for entry : XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX & /AC04/ XXXXXXXXXXXXXXXXXXX, in this case AC04 is code. want to capture below code by using if formula, they can start AC or AM or BE or AG or BT or MS or PY or RS.

    1 AC01
    2 AC02
    3 AC03
    4 AC04
    5 AC05
    6 AC06
    7 AM01
    8 AM02
    9 AM03
    10 AM04
    11 AM05
    12 AM06
    13 AM07
    14 AM08
    15 BE01
    16 BE02
    17 BE03
    18 BE04
    19 BE05
    20 AG01
    21 AG02
    22 DT01
    23 MS01
    24 PY01
    25 RF01
    26 RC01
    27 RC02
    28 RC03
    29 RC04
    30 RR01
    31 RR02
    32 RR03
    33 TM01
    34 X1!c2!n

    Please can you advise a formula for this.

    Thanks & Regards
    Ashish Baddar

  20. Hi there,

    Good day to you, i have a XLS with 2 Tab Aging and score card. I have different value for column "W" in aging tab and i would like calculate the below.

    1. to calculate the total for all the different texts which are not "abc" and with a specific aging day.

    ** Please Help**

    2. to calculate the total "abc" and with a specific aging day, which i mange to do it.

    =COUNTIFS(Aging!$W:$W,"abc",Aging!$B:$B,ScoreCard!J18,Aging!$N:$N,"<15")

  21. Hello i am having a Count if issue

    the formula i am using is:
    =OFFSET('Raw Data'!$K$5,1,0,COUNTIF('Raw Data'!$K:$K,">0"))

    i am using this in name manager to automatically update a control chart as i dump data in my "Raw Data" tab.

    the chart is a representation of a shipments date (x axis) and the analysis of the shipment on that date.

    the issue that i am having is that the formula is not counting the entire column. Furthermore my chart is still picking up the zeros

    there is a large amount of entries in the column (J5-J65 the formula above stops at J26. i can not figure out why.

    1. Im sorry

      in the range above i mean K5-K65 and stops a K26

  22. You're really great!!

  23. hi Svetlana Cheusheva..

    I think you are a MVP.

    I had gone through the your website and found that you have helped alot of people in solving excel problems. .

    I really appreciate your efforts.

    I want to be excel expert like you tell me what should I do for that...

    God bless you..

    1. Hello Bharat,

      Thank you very much for your kind words. No, I am not an MVP, but I am lucky to work with very talented and knowledgeable people and I've learned a lot from them. Reading also helps : )

  24. Hi, I have a workbook with about 6 worksheets.
    Each worksheet has to be filled with various values name age ....
    the last worksheet is a 'report worksheet' that has to pick up input data per month from the other worksheets, undergo some formulas and then display the result.
    This is all fine.

    My problem is the other worksheets are filled in on a daily basis on the same sheet over many months BUT the report has to dis-aggregate the data by months to be input into the report. n.b. There is a month column.

    Now if i have a formula such as =COUNTIF('SHEET1'!AF9:AF608,"FEMALE")
    how can i manipulate the formula into picking up the no. of 'females' in 'sheet1' in march only
    n.b. there is a seperate 'report worksheet' for each month.

  25. I have the exact same problem as Donna (Q95). Essentially, I'm trying to get a single total of the number of projects where the actual completion date (column B, for example) exceeds the proposed completion date (column A). I’ve tried various formulas, including the one Donna noted, with no success. Any help would be greatly appreciated.

  26. Hi there

    I am trying to find out a way to count the dates in multiple cell range : in My excel sheet

    30-Sep-14
    30-Dec-14
    30-Jul-14
    30-Jul-14
    30-Jul-14
    30-Jul-14
    30-Sep-14
    30-Aug-14
    30-Aug-14
    30-Dec-14
    30-Dec-14
    30-Dec-14
    7-Oct-1
    30-Sep-14
    30-Dec-14
    30-Dec-14
    30-Jul-14
    30-Dec-14
    7-Oct-14
    there are dates in column range O3:O46,L3:L47,R3:R51,U3:U51,X3:X50

    i am trying to get the count of date which fall in within particular months i.e. April 14, May 14,June 14.July 14.August 14,September 14.....

    i am using this formula but not getting any result, thanks in advance for any help.

    =COUNTIFS(O3:O46,L3:L47,R3:R51,U3:U51,X3:X50,">=1/1/2013", O3:O46,L3:L47,R3:R51,U3:U51,X3:X50,"<=31/1/2013")

  27. Hi I am trying to count the number of clients within ranges.
    example:
    all clients who are >= 10 but =10")and=COUNTIF(H3:H1659,"20")

  28. Hi Svetlana,
    Thank you for your informative post and support! I dates in 2 columns: one column (M) for "original planned date of project completion", and another column (N) with "latest planned date of project completion". I want to count the projects that have a latest date later than the original planned date, i.e. "count if the date in column M is greater than the date in column N" specifically trying this formula COUNTIF(M3:M22,">L3:L22"). It is not calculating correctly - any ideas?
    Thanks again!!!

    1. Hi Donna,

      The COUNTIF formula cannot help in this case because you need to compare 2 ranges. Try the following array formula (remember to press Ctrl+Shift+Enter) to enter it correctly:

      =SUM((M3:M22>L3:L22)*1)

  29. Hello!
    Im trying to count blank cells in a column in my spreadsheet, but it wants to count the whole sheet and not just the ones included in my table. How can i do it so it only counts blanks that are part of my table (they have other information in their rows). Any suggestions on how to do this?
    Thank you!
    Hailey

  30. Hi - hoping you can help.

    I need to count column B (which are various ID numbers), remove the duplicates but only count them if Column A says "BIP" and Column F says "Active"

    Is it posible to do this in one formula??

    Thanks heaps

  31. Hi
    I want to be able to do a COUNTIF across 2 spread sheets but for 1 cell in both sheets, so D2 in one spread sheet will be the same as D2 in the other. However, I want to use the count if option to do this. So if D2 was blank in one sheet, it would be blank in the second sheet, but if D2=2 in the first sheet then it would be 2 in the second.
    Currently the first sheet will be blank but the second sheet will be 0 as it is linked to the first sheet. But we need this to be blank.
    Hope this makes sense, thank you.

  32. Need some help here I have analyzed 10 Columns of 190 numbers and have counted 321 matches I want to take the 321 numbers scattered throughout the various columns and put them in another column in order of how they appeared. Any idea how I could lookup, find and get the data into a new column? The thing is as new data goes in the numbers of matches can vary between 1 and 1900

    Thanks

  33. I'm trying to count non duplicate using
    =SUMPRODUCT((COUNTIF(L13:L567,L13:L567)=1)*(L13:L567""))

    but when I filter other column to unique name, the total remaining the same. How it will be possible by using same formula? Thanks.

  34. On a yearly spreadsheet, I have numerous entries with dates throughout the year. If I want a count of how many entries there are for a given month, January, February, etc., how would I do that? Also, the entries are on one worksheet and the count will be on a different worksheet (in the same workbook), how would that formula be written?
    For example:
    1/10/15
    1/18/15
    2/17/15
    3/4/15
    Count=2 (for January)

  35. Hi, i want to use countifs function , to assign rank to the people

    FOr exp.

    Column A: List Emp Name
    Colum B: contains Numeric figures achieved by them
    Column C: in this i want Rank( high to low), means the employee got the highest number in the list, should be ranked as "1", as he is the topper.

    Can i use it for this ?

  36. Hello,

    I want formula like this:

    for ex : if row 4 have value in anywhere will find and then take text or value from upper row's - example formula found f4 value then will take f3 text or value...

    Thanks

  37. Hi i need to count the sum of the failed but the problem in it i that one column there are two different data i mean on the same day
    thanks

  38. I'm trying to count the output of an IF function. It seems that the COUNTIF function can't read the output of the IF function as data, but only the function behind the output. Is this true? Can I use COUNTIF to count output form an IF formula?

    Thanks,

    Lissa

  39. Hi Svetlana,

    Very happy new year to you!!

    Please can you help me on below query.

    I have a table to calculate balance leaves each time number of leaves taken are entered it would deduct from the available no of leaves for the period. Now i am trying to apply the formula =SUM($K$8-G12) to the whole column M. Now i want to add a condition that if the Col G is blank, then M should also have blank value otherwise it would count as per formula =SUM($K$8-G12) which is Total no of leaves - leaves taken = balance leaves.

    Thanks,
    Rachana

  40. Very useful for analyzing the marks scored by the students. I want a formula (countif function) for finding the marks scored by the students from 1 to 4 (like that 5 to 17; 18 to 24; 25 to29; 30 to34; 35 to 44;45 to 49). Range is marks scored by students for 50 marks. I kindly request you to please intimate the criteria for which I will be ever grateful.
    Regards,
    Sujatha Victor

  41. I want to know how to use the COUNTIF Formula if we want to count number of customers whose second letter of their names is as "A"

    1. Hi Hoda,

      Try =COUNTIF(A2:A100,"?A*") where A2:A100 is the names column.

  42. i have got many sheets in my workbook.but the problem is that i have to create pivot tables of only those sheets which end with Total??? Please help please ---Thanks in advance

  43. Hello, I am making a spreadsheet for a sales team. I have the formulas to count the deals they have. I have salesperson 1 (column I) and salesperson 2 (column j). My formula is ((countif(i5:i29,"mike")+(countif(j5:j29,"mike").

    However, when there is a salesperson two, then both sales person 1 and salesperson 2 are
    Only allowed a "half deal" meaning it should
    Count .5 for both rather than full.

    Can you help?

    Thank you so much

  44. Hi, below is my excel sheet , can we write a power query to find number of session( start to End)
    Expected result for below excel is 2 , if suppose A12 had End Session the count to be 3.

    A
    1 Start session
    2 ...
    3 ...
    4 ...
    5 End Session
    6 Start session
    7 ...
    8 ...
    9 End Session
    10 Start session
    11 ...
    12

    I do have a huge data in working excel (taking lot of time using vba script ). please help to find a solution.
    Thanks in advance,
    Sunil

  45. Hi, below is my excel sheet , can we write a power query to find number of session( start to End)
    Expected result for below excel is 2 , if suppose A12 had End Session the count to be 3.

    A
    1 Start session
    2 ...
    3 ...
    4 ...
    5 End Session
    6 Start session
    7 ...
    8 ...
    9 End Session
    10 Start session
    11 ...
    12
    13
    I do have a huge data in working excel (taking lot of time using vba script ). please help to find a solution.
    Thanks in advance,
    Sunil

  46. hi Svetlana,
    how to count cells by identification of the cell color.

  47. =AND(COUNTIF($A$1:$P$261,A1)>1,A1>0)

    Good day,

    I am trying to highlight all duplicate dates that match on a worksheet, the above formula is a conditional format that works, but it also highlights text values.

    I originally wanted to create a conditional format that referenced a column on another worksheet(called A), and then highlighted those dates that corresponded in a table on worksheet(called B). But I could not get it to ignore blank cells or cells with text in them. I tried many, many, times.

    I am probably too much of a novice in my understanding of how formulas work.

    Any help you can offer would be very much appreciated.

  48. =COUNTIF(tblJanuary[@[1]:[31]],"V")+COUNTIF(tblJanuary[@[1]:[31]],"A")+COUNTIF(tblJanuary[@[1]:[31]],"S")+COUNTIF(tblJanuary[@[1]:[31]],"H")

    I want to count the number of certain values (V, A, S, and/or H) in row, but I do not want it to count any other values I might enter. I tried the above formula but it still counted if I enter something other than V, A, S, H. Any ideas?

    1. Nevermind - Actually, it is working! Happy Holidays!

  49. Thank you so much for taking the time to share your knowledge! I was wondering, is there a way to calculate the % of numbers within a column that are greater than another value? I work in education, and I have very long lists of students names and their scores on any given assessment. I would like to, for example, calculate the percentage of students who scored greater than or equal to 42%. Is this possible? I can sort and do the calculations manually, but the larger my lists get, the more errors I have. Thank you!!!
    A B
    Bill 24%
    Chris 82%
    Ringo 19%
    Paul 96%
    John 94%
    George 88%
    Mick 16%

  50. OMG! Thank you!!! This helped out so much!

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