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

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

  2. You're really great!!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  31. Hello,

    Thank you for this post that I must admit is very useful for people introducing Excel. I would like to know something, I'm trying to put out duplicates from a column but the thing is, by adding all the data I'd like the formula to count cells from number (scale number) 402 to 460 and so on for 502 to 560 etc.

    Here is a look on the formula :

    =SUM(IF(FREQUENCY(MATCH(A2:A749,A2:A749,0),MATCH(A2:A749,A2:A749,0))>0,1))

    and then this is what i'd like to add inside the previous one :

    =COUNTIF(A2:A749,">=502")-COUNTIF(A2:A749,">560")

    A2:A749 datas are numbers ONLY

    To sum up, I want to count all the cells except duplicates and only from number 502 to 560

    Thank you for your advice!

    Sam

  32. Hello Svetlana and everyone,
    I had a small question regarding data analysis. I tried looking COUNTIF and FREQUENCY. I want to quantify following things in my data set
    I want measure the number of events where 0 appeared 5 times or more, consecutively in a given data set. (e.g.: 000001100010000000 (here zero appeared consecutively, twice, 0=5 and 0=7))
    Is there any formula to do this?
    Thanks in advance
    Cheers
    Ameya

  33. I have a worksheet that currently counts the number of rows in a worksheet that are populated in certain columns:
    =COUNTIFS(Andrew!E2:E998,"*",Andrew!F2:F998,"*",Andrew!H2:H998,"*",Andrew!J2:J998,"*")

    this method of conting is used to count for each tab in the workbook and is sumarised with the formula: =SUM(F20:N20) to produce an overall value.

    There are 5 of these sheets that are linked into a master sheet that counts the =SUM(F20:N20) from each book and produces a grand total.

    Is there a function that will allow me to produce a list of rows that satisfy the formula buy way of a report?

  34. Hi Svetlana,

    I am trying to count the number of cells in a column which are all populated with text. I am trying to count the amount of cells in that column which contain less than 10 words? how do i do this?

    Many thanks,

    Courtney

  35. I have a Question and its probably easy for you to work out, but it's bugging my head out.. Ok, I have some Data and its in columns Based format running down one column each L, A, B, each l,a,b, if that makes sense running down the Spreadsheet is, the L value is Averaged out and each L ,A, b,value is posted to each other Cell averaged out.

    Each L,B,A Value is averaged out,OK. The value is posted to another Cell and the Cell calculates a Positive value, Greater Than Positive or a Negative, the worked out Value is posted to another cell. The then value is Dynamic, meaning its not the same based on Human input moving a threshold up or down. It reproduces a value thats not constant until a actual result is put into the formula Cell.. I want it to collect a value from a cell with a formula thats Dynamic....

    I want to work out a Greater than and less on this Dynamic Cell. But its producing a Zero Result this Dynamic.
    If at all you need a video or screen representation then i will look at that option..

  36. Hi Svetlana,

    I have a main summary sheet where i want the count meeting below criteria in a cell.

    I have sheet1 and summary sheet. Sheet1 has Col F with all the names of the officer like Tina, Mira etc. Col I has months like March,April etc. And Col J has the target dates of visit after 6 weeks from the tenancy date. This col J also has the date in different year 2013, 2015,2016 so on, e.g. 01/05/2014, 31/03/2015 etc.

    Now from sheet1, a cell in summary sheet should check for the names from Col F e.g. Tina, then it should take count all the April Months from Col I against Tina's name, and should count only the dates that fall between 01/04/2014 till 31/03/2015 against Tina's name.

    1) To select the names from F col from sheet1
    2) to count months against a name from col I
    3) to add a condition that the visit target date - 6 week which is in col J should fall from 01/04/2014 to 31/03/2015 against the selected name

    Please can you advise a formula for this.

    Thanks in advance,
    Rachana Ranpura

  37. I am trying to do something much simpler. I would like to count the number of different items in a list and just by specifying the column as a range. Can this be done?

    1. The closest I have got is

      =SUMPRODUCT(1/COUNTIF('Sheet1'!A1:A8000,'Sheet1'!A1:A8593))

      Where there are 8000 lines in the spreadsheet. However if I put in 8001, I get a divide by zero error. It would also be preferable if I could ignore the top line as it is headings but I can jus -1 if needed

  38. Hi,

    Please help
    I have data this

    Folder Pax
    TU1 2
    TU1 3
    TU2 1
    TU2 5
    TU3 6
    TU3 1

    And I want
    Folder Pax
    TU1 5
    TU2 6
    TU3 7

  39. Hi im wanting text "IN" to equal 2 when typed in how do i use countif to add 2 toa total amount

    thanks Liam

  40. I have a very long table. This is just an example of the table:

    A1: Joe

    6:00 am 7:00 am 8:00 am 9:00 am

    User Date Activity Description
    Joe 11/4/14 6:10:08 am Build Build castle
    Joe 11/4/14 6:10:10 am Build Build castle
    Joe 11/4/14 6:15:08 am Build Build castle
    Joe 11/4/14 6:30:08 am Build Build castle
    Joe 11/4/14 6:39:08 am Build Build castle
    Joe 11/4/14 7:10:26 am Build Build castle
    Joe 11/4/14 7:20:39 am Build Build castle
    Joe 11/4/14 7:25:26 am Build Build castle
    Joe 11/4/14 7:50:39 am Build Build castle
    Robert 11/4/14 8:00:00 am Build Build castle
    Robert 11/4/14 8:10:08 am Build Build castle
    Robert 11/4/14 8:15:00 am Build Build castle
    Robert 11/4/14 8:20:08 am Build Build castle

    I need to find a way for excel to give me how many time gaps are greater than 10 minutes between 6am and 7am, and input that in one cell. Also, I need in another cell the total time of those 10 minute gaps for that same hour. I need these calculations only when a specified user is entered in cell A1.
    Can you help me with this?
    Thanks, Willie

  41. need to distinct sum ,distinct count,Distinct Average

    values in cell can be blank.

    if have found two way to do distinct count but could not found how i can do the other
    =SUMPRODUCT((A3:A21"")/COUNTIF(A3:A21,A3:A21&""))

    =COUNT(IF(FREQUENCY(B3:B21,B3:B21)>0,B3:B21))

  42. Just wanted to say a big THANK YOU for this article. Buried in the article is a short paragraph about how to use the symbols as text in a countif(s) and it has really helped. Thank you for taking the time to publish this comprehensive explanation.

  43. Hi - I'm trying to compare two worksheets to see if an email address on sheet 1 is in a range of cells in Column A in sheet 2. I've tried using vlookup and ifcount formulas but neither are working. I removed all formatting from both sheets and I'm using Excel 2013. Can you please suggest a formula or point me in the right direction? Thank you!!

  44. I want to count how many names appear in range C5:C11 from a list of names that I have. That list I use in drop down menus in the range C5:C11. My names are Reid, Tyrone, Chris, and Paul. I can select those names from the drop down menu. I want to count how many names from the list are in that range. I tried countifs with not sucess

    1. Hello Omar,

      You can use one of the following formulas:

      =countif($C$1:$C$5," Reid") or =countif($C$1:$C$5,F2)

      Where cell F2 contains the name Reid.

  45. I'm having an issue with compiling certain data.

    I'm trying to find an overall percentage of participating users for certain questionnaires they have answered. For example, I have:

    =(COUNTIF('Sheet 1'!$C4:$Z4, "*CHI*")+COUNTIF('Sheet 2'!$C4:$Z4, "*CHI*")+COUNTIF...and so on for 32 sheets.

    While that's fine and all for those who participated in each questionnaire: ...+COUNTIF('Sheet 32'!$C4:$Z4, "*CHI*"))/(COUNTIF('Sheet 1'!$C$1:$Z$1, "*CHI*")+COUNTIF(...up to sheet 32.

    $C$1:$Z$1 being my headline, line 4 being this particular individual's responses (where "CHI" might not exist in certain sheets)

    The issue I am having is that there are many individuals who couldn't complete all the questionnaires (we're talking hundreds). I don't want to have to go sheet by sheet, line by line to find and specifically eliminate which sheets they didn't complete to get the proper percentage (they didn't do sheets 12, 14, 20-22, 25 for example, and I would have to delete those COUNTIF segments from the formula, just for that one cell so those extra 6 aren't mistakingly reducing the individual's percentage).

    Is there an IF or COUNTIF formula that can count an entire row only if there is a timestamp (proof they filled the questionnaire) next to the individual's name?

  46. I have an excel spreadsheet that lists the different subjects that a student has undertaken.

    Example (cells N2:N9):

    Information Technology
    Physics
    Advanced Maths
    General Maths
    English Research
    English Critical
    English Academic
    Computer Applications

    I would like to insert a formula underneath this (in cell N10), to look at the subjects the student has undertaken, and return a result based on this search. I need to identify if a student has undertaken all 3 English subjects listed. I have tried multiple formulas, but cannot seem to get the right outcome, as I am searching on a range of cells (N2:N9).

    Thanks

    Matt

    1. Hello Matt,

      Please try the following formula.
      =IF(COUNTIF(N2:N9, "*engligh*")>=3, "Pass", "")

      If it is not what you are looking for, please describe the result you expect to get in more detail.

  47. Hi there

    I want my excel spreadsheet to count letters in a row but with a unique condition behind each letter i.e A=1, B=2, C=3, D=4, E=5, F=6 G=7

    so if the cell in row F has b and row G has E total will 7

    is there a way to do this?

    jai

  48. Hi there-
    I was wondering if I can use the COUNTIF function with a very specific conditional test. I have a set of data by date where for one date in column A, there might be 4 corresponding numbers in column B. Example:

    9/8/2013 43
    9/8/2013 45
    9/8/2013 36

    Is there a way to use COUNTIF to only count the appearance of a number greater than x once per date? For the above, I would want it to only count once if the criteria were ">35".

    Thanks,

    Steve

    1. Hi Steve,

      Since you want to count with two conditions, you have to use COUNTIFS rather than COUNTIF:

      =COUNTIFS(A:A, "9/8/2013", B:B, ">35")

  49. Hi Lydia,

    Your second comment made the task clearer.

    Since you have to sum by several conditions you will have to use the COUNTIFS function rather than COUNTIF. The formula can be as follows

    =COUNTIFS(A:A, F2&"*", B:B, F3, C:C, ">="&F4, C:C, "<="&F5)

    Where:

    F2 - the cell containing either N or M
    F3 - the cell with "New Birth" etc.
    F4 - the sell with the "start date", e.g. 1/4/14
    F5 - the cell with the "end date", e.g. 30/6/14

    Naturally, you can put the above conditions directly into the formula, but in this case you will have to re-write it for each set of conditions.

    You can also use the above formula in another worksheet, by adding the worksheet's name before the ranges, like this:

    =COUNTIFS([Book1.xlsx]Sheet1!$A:$A,F2, [Book1.xlsx]Sheet1!B:B,F3, [Book1.xlsx]Sheet1!C:C,">="&F4, [Book1.xlsx]Sheet1!C:C,"<="&F5)

    1. Thank you so much Svetlana, you are a a life saver, thank you so much for your time. I am trying the first part then I will venture on to the answers going in to another worksheet. But I am guessing or hoping IO can use the paste link to do this! Thanks again

  50. I have work book 1 which collates a range of data.
    Column A - codes N2, N17, M4, M2 etc(only N and M are used, although the following numbers vary)

    Column C - New Birth, 0-4 years, 5+ years

    Column F - shows dates of data entry.

    My question to ask is:

    How many N or M (column a) have Newbirths (Column c) between the dates 1/4/14 to 30/6/14 using the dates in column F
    Then how many N,M 0-4 yrs in above dates etc
    There will also be 3 further date ranges, 01/07/14 to 30/09/14, 01/10/14 to 31/12/14 and finally 01/01/15 to 31/3/15.
    hope this makes sense

    Ideally I would like to ask additional questions for calculations possibly in a new work book, but with all data linked so it would be constantly updated. I have briefly learnt how to link 2 workbooks together.

    1. apologies I have sent the same question

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