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 6. Total comments: 575

  1. I am trying to use the count unique values function as part of a COUNTIFS equation, I need to count the amount of 'P's in one column, that have a unique number in another column. I can't get the unique equation to work though. Please help.
    =COUNTIFS(E$7:R$42,">0",F$7:S$42,"P",E$7:R$42,"=1") Where did I go wrong?

    1. Hello Zack,

      You need a helper column that will show whether the number in another column is unique or not.
      The formula can be as follows:
      =if(countif(D:D,D2)=1,1,0)

      Then you can use a value from this helper column as one of the conditions for your COUNTIFS.

      As I can see in your formula, several columns are used for each range. Is this correct?

      1. Yes that is correct, the way that we have the page formatted is largely for viewing ease.

  2. Hi, Is there any formula to capture data from other cell if a particular cell is blank, for example A1 and B1 are the cell,if A1 is blank then data should be capture from B1.

    Regards
    Ashish

    1. Hi Ashish,

      Try this formula: =IF($A1="",$B1,"")

  3. Hey Swetlana,

    Please can you advise on below 2 quesries.

    1) I have A column that shows a start date e.g. 10-1-2014 and the task needs to be completed by 6 weeks. So i want the B column to auto calculate the date of completion that is 6 weeks from the start date. Please can you advise how can formulate this one. And like also want to calculate for other target dates i.e. after 26 weeks in another column and 52 weeks in another column.

    Col A(start date) Col B(6 weeks target date)
    10-1-2014 ? (How to auto populate date)

    1) Now I have two columns as below, please can you advise.
    Column B shows - target date to be completed and the column C is showing actual date for the task completed. Now i have column D where i want value Y or N based on date of completion is greater or lesser than the target date. So if completion date is greater than target date than it should show N on Column D and similarly Y where the date is equal or lesser than the target date. Please can you advise how can set a formula for this one.

    Col A Col B Col C Col D
    start date 6 weeks target date Actual date Y or N ?(auto populate)
    01-09-2014 ? Auto populate 20/10/2014 N - Value to auto populate

    Thanks,
    Rachana

    1. Hello Rachana,

      Here's the formula for column B (cell B2): =A2 + 6*7
      And this one is for column D: =IF(C2<=B2, "Y", "N")

      1. Thank you Svetlana :) It worked

        Now when i use =IF(C2<=B2, "Y", "N") and along with that i want to add one more criteria that if the completion date column is blank then the Y/N column should remain blank. Please can you advise.

        Thanks,
        Rachana

        1. Rachana,

          Assuming that your "completion date column" is column C, here is the formula:
          =IF(C2="","",IF(C2<=B2,"Y","N"))

          1. Thank you Svetlana for formula =IF(C2="","",IF(C2<=B2,"Y","N"))
            It worked as desired. :)

            I would need your help on below to get a formula.

            I have a A column with start dates in the format e.g. A1= "01/01/2014", A2= 1/3/2014, A3= 1/2/2014. Now i want column B to auto enter the value in B1 = January, B2= March, B3= February..

            Just to highligh that the Start date is not in order hence the months are not in order too. please advise which formula can be applied to have respective months value in B column based on Start date in A column.

            Thanks,
            Rachana

            1. Rachana,

              You can simply enter the formula =A1 in cell B1, and then copy it down to other cells of column B. This will populate column B with dates corresponding to column A. Then select column B, right click and click Format Cells > Number tab > Custom, and type mmmm in the "Type" box. mmmm is the date format that displays months only.

          2. Hey Svetlana,

            Lets say an A column has Start date that is blank and i want to add a formula on C stating that if A1 is blank then C1 should be blank but A1 has a start date then it should calculate A1+6*7 for the date to auto populate on the 6 weeks date. Please can you advise.

            From your previous reply i see we can use =IF(A1="","") for having blank cell but not sure how to further add the criteria as above. Please advise me.

            Thanks,
            Rachana

  4. If a sheet arranged as bellow
    Col A col B col C
    Name rank age
    Arun dy co 25
    Philips si 30
    Joseph dy co 27
    Vinod dy co 29
    stphen si 26
    Then how can count how many dy co become between the age 25 to 30 by using excel formula.
    Please help

    1. Hello Satheesh,

      You can do this using the following COUNTIFS formula, that returns the count of "dy co" between the age 25 to 30, inclusive:
      =COUNTIFS(B:B,"=dy co",C:C,">=25",C:C,">=30")

      The below one returns the count of "dy co" between the age 25 to 30, not including 25 and 30:
      =COUNTIFS(B:B,"=dy co",C:C,">25",C:C,">30")

      For more info about using the COUNTIFS function with multiple criteria, please see this tutorial:
      https://www.ablebits.com/office-addins-blog/excel-countifs-multiple-criteria/

  5. Hi Svetlana,

    What formula can i use if i want a value "N" in 1st coulmn based on blank cells that appear in 2nd column and want value "Y" in 1st column based on non-blank cells that appear in 2nd column. Please advise.

    Y/N Names
    N
    Y abc

    Thanks

    1. Hello Rachana,

      You can use the following formula for your 1st column:
      =if(B2<>"", "Y","N)

      Where B is your second column.

      1. Thank you Swetlana!!!

        One more help i would need on a formula.

        I have one column "A" with names e.g. Mike, Tony etc goes till A12, and the names are repeated.
        other column "B" having some numbers with % sign e.g. 90%, 80% etc.. goes till B12

        Now i want to count non blanks ones in B1:B12 for Mike in B1:B12

        I tried as below , but it didnt work. Please can you advise which forula fits better..

        =countifs('Block Inspections'!B1:B12,'Block Inspections'!A12:A150,"=Mike")

        Thanks,
        Rachana

  6. HELLO,
    Please help me in this problem

    Formula: =IFERROR(LOOKUP(C2,Sheet1!$A$30:$A$39,Sheet1!$B$30:$B$39), "")

    Required condition: If C2 cell in not equal to A2 " additional conditon to be added in above formula with the existing.

    1. Hello Vamsi,

      Please specify what exactly additional condition you want to add, and in what part of the formula you want to add it.

  7. I have a spreadsheet where I input my current weekly sales in Column C and last year's weekly sales is already typed in Column B; I want to make a REAL month-to-date weekly comparison at my weekly manager's meeting.

    To do this I would need the weekly sales from last year in Column B to count ONLY when if I input the weekly sales figure in Column C; otherwise I need Column B "blank" or to count as "0".

    Note: please keep in mind that last years weekly sales in Column B is taken from last year's monthly sales in Column A divided by 4 (accounting for four weeks in a month) whereby the value of cell B1 is "=A1/4"

    So, I need Column B to display last year's weekly sales ONLY when I input last week's sales in Column C.

    Can someone please help me?

    Thank You,
    Johann Sebastian

    1. Hello Johann,

      Enter the below formula in cell B1:
      =If($C1<>"",$A1/4,"")

      And then copy/paste it to the whole column.

  8. Does the COUNTIF function only compare the first 15 characters? I use it to check for duplicate values. It flags 2 of my cells as being duplicates. The only difference is the 16th character.

    1. I tested this theory of mine using the right 15 characters of a concatinated string. That fixed the problem.

      1. Hello Rick,

        Thank you for the information and the update.

        To my best knowledge, the COUNTIF function has 255 characters limitation since Excel 2007. And one can bypass this limitation by using the Exact function in array formulas.

        The issue you describe may occur either in a very old version of Excel, or more likely, if your sheet contains only numeric data and Excel treats it as numbers. As you probably know, Excel has a 15 digit limitation when it comes to numbers, and after the first 15 digits, it replaces everything with 0's. Please check out the following Microsoft answer for more details:
        https://answers.microsoft.com/en-us/office/forum/office_2007-excel/15-digit-number-limitation-non-text-workaround/a4974853-7c3c-4830-8562-2e88369d981b

  9. Can you please tell me what the formula is if I want when if a # is less than 35 then add $5.75 if it's greater than 35 add $12.75 please help thanks

    1. Hi Karla,

      I am not sure I can exactly follow you. Do you want to enter a number in some cell based on a value in another cell? For example, if a number in cell A1 is less than 35, than put 5.75 in cell B2, if it's greater than 35, than put 12.75? And what if it is equal to 35?

  10. Hi,
    I want to know from you that how can I count the two cell data at a time like:

    ColA Col B
    1 M
    2 F
    3 F
    6 M
    2 F
    3 M

    I want to know for 1 how many M of F and for 2 how many M or F Like ------------

    1. If you have got answer pls. share with me. Even I also need solution.

  11. I am trying to identify and account for duplicates. I would like the COUNTIF formula to tell me if there is a duplicate anywhere else in that column. If a unique value it would result in a "1" and if a duplicate is found it will result in a "0." However, I would also like the 1st occurance of a value to result in a "1" and all other duplicate values to be a "0." How can I get that 1st occurance of a value to result in a "1" separate from its duplicates?

    1. =IF((COUNTIF($A$1:$A1))=1,1,0)

  12. Is there any site where we can get online support for MS office (Excel) I mean through chat...

    1. Hi Bilal,

      I am sorry, I do not know any website that provides free online consultancy / support for Microsoft Office.

  13. I tried using the countif(range,""&"") to count the cells within a range in my sheet that have something written in them and it keeps counting all of the cells selected.

    If I tried counting all the cells that are blank instead and write countif(range,""&"*") it brings me to 0.

    Any idea of why it wont count my cells that have data?

      1. Hi Svetlana,

        Unfortunately when using both functions to count non blank cells it still counts all the cells for me.
        The way I input the function is : =COUNTIF(E21:E27,""&"")
        Is that a correct way?
        I only have test1 in 1 of the cells written yet that function comes out as 7.

        Any ideas of what might be going on in the excel?

        1. Hi Samantha,

          This is the correct formula to count non-blank cells.

          =COUNTIF(E21:E27,"<>"&"")

          Does it work for you?

          1. Hi,

            I also tryed this formula but it counts cells with "".

            1. Try this to exclude blanks
              =COUNTIF(E21:E27,">"&" ")
              Space in double quotes

              and this to only include blanks

              =COUNTIF(E21:E27,"<"&" ")

              1. Thank you Mike M, I've been trying to countifs with a blank string "". Doing what you said to exclude blanks works like a charm.

  14. Hi Svetlana!, I hope you can help me...

    I am making a sample-test exam where the correct answer will be hidden in column C and column B will be where the person who is taking the exam will place his/her answer. I need a formula to tell me how many correct answers were given. For example:

    -X1- = test takers' answer
    -X2- = correct answer

    -X1- -X2-
    A B
    C C
    A D
    D D
    B B

    I need a formula that will tell me that the test taker answered 3 questions correctly and then divide that value by the number of questions, in this case 5 questions. FYI: The answers will be side-by-side, however many blank rows will separate each answer (to facilitate the question and multiple answers in column A).

    Thank you,
    Rod

    1. Hi Rod!

      You can use the following array formula. Change 30 to the number of your last row with answers, and remember to press Ctrl + Shift + Enter to complete the formula:
      =SUM(NOT(ISBLANK(C2:C30))*(B2:B30=C2:C30))

      Then you can divide the result by the number of questions.

      BTW, please be aware that one can easily see the content of a hidden column. More details are in this blog post:
      https://www.ablebits.com/office-addins-blog/excel-unhide-columns/#disable-unhide-columns

      1. OK, I gound what's wrong... i didn't know that brackets {} are added automatically and I don't have to enter them manually :)

        1. Hi Uldis,

          It's great that you sorted this out! Those brackets might be confusing indeed, I added them to show that is an array formula. But may be I'd better remove them not to confuse someone else :)

      2. Hi Svetlana!

        I tried to use this sample You provided for Rod, but it doesn't seem to work, and I don't get what's wrong...

        1) I entered some values in B and C columns
        2) entered formula exactly as it is in Your sample and pressed Ctrl+Shift+Enter
        as a result I see that formula as a text, and no result... why is that so? What am I doing wrong?

        Here is the screenshot how it looks:
        http://screencast.com/t/QRsaafuSvzw

        Hope, You can help!

        P.S. This is a really nice and helpful blog post!

  15. HELP! I'm trying to create a list of email addresses, based on wireless carrier. So if one column has their wireless number, next column has carrier, I then want to combine wireless number with the appropriate text email. Where these are the extensions I want to add:

    Verizon = @vtext.com
    AT&T = number@txt.att.net
    Sprint = number@pm.sprint.com
    Tmobile = number@tmomail.net

    So if someone lists
    5556667777 Verizon
    6667778888 Sprint
    How do I create a cell that says If A6=Verizon, then 5556667777@vtext.com??
    It's a list of a few hundred, so I'd rather no do manually. Thanks so much for the assistance!

    1. Hello Virginia,

      To fulfill your task, you need to create a lookup table "CarrierName = Domain" in another worksheet. Then, you will be able to pull a domain by the Carrier name using the below formula below and concatenate it with the telephone number. Here is the formula:
      =IF(AND(A2<>"", B2<>"", MATCH(B2,WirelessCarriersData[Name], 0)>0), A2 & VLOOKUP(B2,WirelessCarriersData,2,FALSE),"")

      Where A2 is the first cell in the Phone column not including headers, B2 is the first cell in the Carrier column, and WirelessCarriersData is the name of the lookup table.

      We have created an example for you, feel free to download it using the link below:
      http://www.ablebits.com/_img-blog/_comments/virginia.sorensen@capitalone.com-1.xlsx

  16. Hi

    Im also after something similar to Robert.

    In column A i have a number of different status ie. "On Programme", "Rejected", "Completed" etc. and in column B i have a number of programmes ie. "Cleaning", "Customer Service", etc and i want to could how many for example On programme cleaning there are?

    Ive tried and other formulas similar to this but none seem to be counting what i want.
    =SUM((COUNTIF(A2:A32,"On Programme"))*OR((COUNTIF(B2:B32,"CLEANING"))))

    Can you help?

    1. Hello Lara,

      The same as Robert, you should also use the COUNTIFS function rather than COUNTIF, because COUNTIFS can count cells based on multiple criteria. The formula is as follows:
      =COUNTIFS($A2:$A32,"On programme",$B2:$B32,"CLEANING")

      You can find the full details in this tutorial:
      https://www.ablebits.com/office-addins-blog/excel-countifs-multiple-criteria/
      If you want to use the SUM function, you should enter the following array formula (remember to press Ctrl + Shift + Enter to complete it):
      {=SUM(($A2:$A22="On Programme")*($B2:$B32="CLEANING"))}

  17. I'm after something similar to what you were asking Ruth please.
    I've set up the formula to count up all the instances of "*banananas*" in column B.
    I then have a list of places in column A. So say the total amount of banananas is 300 I want to know how many banananas I have for each of the individual places. So it only counts the banananas entries that are next to specific text in column A.
    Thanks.

  18. Hi There,

    I am wanting to extract some values and I thought COUNTIF might be able to do it.
    I have a cell that contains some data eg: ABC-BBC-ABC-SII-BBC-ABC
    I get COUNTIF to count how many times BBC appears in that cell, seems it only counts the FIRST instance of BBC.
    How do I get it check the entire contents of the cell and correctly record the result eg: BBC appears twice in that cell

    Thanks!

    1. The COUNTIF function cannot count specific text or characters inside a cell, it deals with the entire contents of a cell, as if you selected the option "Match entire cell contents" when performing search in Excel.

      Please use this formula instead:
      =(LEN(A2)-LEN(SUBSTITUTE(A2,"BBC","")))/LEN("BBC")

      Where A2 is the cell containing "ABC-BBC-ABC-SII-BBC-ABC", and "BBC" is the search text. Instead of "BBC", you can enter a cell reference with the search text, e.g. B1, sometimes it is a more convenient way to work with formulas.

  19. Hi,

    I need to combine an IF statement with a COUNTIF that will count 3 out of 5 text entry options for a single column. If the COUNTIF =0, it needs to show as a dash. This is the original formula that is working =IF(COUNTIF(B1:B10000,C1&D1)=0,"-",COUNTIF(B1:B10000,C1&D1))

    I need to add another criteria to the COUNTIFs, where there are 5 possible entries in column E and I need it to count 3 of these (Allocated, Declined, Rejected). Any suggestions is gratefully appreciated.

    Ruth

    1. Hello Ruth,

      Let me check if I understand the task correctly. Do you want to count values in column B only if column E has any of these values: Allocated, Declined, Rejected? Or, are you looking for something different? It will be very helpful if you can paste the values from a couple of rows in columns B-E and the result you expect a formula to return.

  20. Hi,

    I have a project list where activity is shown in a timeline. I need to count up if a column 'project stage' shows activity in any given month. I have set the countif for picking up activity but now need to make it so that it only counts that activity if column 'project stage' is a particular word.

    Any idea?

    BTW the above is great!

  21. Hi There,

    I am trying to record attendance at my office and I am putting together an excel spreadsheet to store the data. I need to be able to sum up the totals of vacation days, personal days, business days, and sick days. The countif works well if I put 'V' for vacation, 'B' for business and so forth, but how can I calculate half days? Is there a way that I can set up a second Countif function so when I put in something like 'VH' in the range it counts it as 0.5 rather than one? Pretty much I am wondering if I can change the amount that the countif function counts by.

    Thanks

    1. Simply divide it by 2, I mean after formula type /2

    2. I'm trying to do something similar here- vacation time used and remaining based on hire date adding in at hire date additional days. so if corey has 15 days now total and used 3 in january and 4 in march and 2.5 in may then gets 15 more in august. how would i write that up? plus i have the variant of half days as well. i understand your count if you used for her issue

    3. Hi Kelly-Ann,

      I think you can just multiply the result of COUNTIF for "VH" by 0.5 to count half days, e.g.:
      =COUNTIF(RANGE, "V") + 0.5*COUNTIF(RANGE, "VH")

  22. Hello,

    I want to formulate the following:

    IF "cell value A" greater than(>) "cell value B", THEN "cell value A" = "cell value B" and therefore the result "cell value C" is equal to zero

    if this is doable can it be applied to multiple "cells"?

    1. Hi Richard,

      I understand your condition but cannot figure out what result you want to achieve : ) If you want a formula to add a zero to column C if a value in cell A in the same row is greater than in cell B, you can use the following IF formula: =IF($A2>$B2,0,"") where 2 is your first row with data. Since we use a relative row reference (without the $ sign) the formula with compare values in each individual row when copied across multiple cell. If you are looking for some other result, please clarify.

  23. Hi Karo,

    Yes, it is possible, but you will need to add a helper column to your table. If this is acceptable, then create an additional column and copy the following formula across that column, which will extract the first 8 characters =LEFT(A2,8). After that, you can proceed in 2 ways.

    Way 1 – using Subtotal:
    - Sort your table by Column A or the newly created column with the above formula.
    - Apply subtotal to the table (Data >Outline >Subtotal) with these settings:
    At each change in : Column with the formula
    Use function: Count
    Add subtotal to: Column with the formula
    - Click Ok.
    - Then Press Number 2 at the left side of you sheet and you will get the count of codes by the first 8 characters.

    For more info about using subtotals please see this article - Using Subtotals in Microsoft Excel

    Way 2 – using a pivot table:
    - Insert a pivot table (select your table and go to Insert > Tables > PivotTable).
    - Place your pivot table onto a new sheet.
    - Drag and drop the column with the formula to the Rows section and column A to the Values section.

    1. Hi there!

      How can I find the duplicates in a single column with the same name but with different format? (e.g. "Susan Doe" "Doe Susan") can I use countif to find the duplicates?

      Thanks in advance

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)