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

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

  2. Hi i am trying to come up with a formula that I can find the duplicate order ID in one column and have it subtract the dates in another column so I can figure out how long it took to ship.

    1. Hello Jeremy,

      I am sorry, your task is not very clear. If you can send your sample to our support team at support@ablebits.com, we'll try to help.

  3. Oops i tried as below with the non blank ones and it worked. :)

    =COUNTIFS('Block Inspections'!$A$12:$A$150,"Mike",'Block Inspections'!$D$12:$D$150,"")

  4. Hey Swetlana,

    I just tried as below as it worked :)

    =COUNTIFS('Block Inspections'!$A$12:$A$150,"Mike",'Block Inspections'!$D$12:$D$150,"")

    Thanks,
    Rachana

  5. Hi Swetlana,

    Just to add on above query, i am counting them on another master sheet.

    Thanks,
    Rachana R

  6. 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 formula fits better..

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

    Thanks,
    Rachana

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

  8. I need a formula that says:

    if column I = cell A1 count it, but if column I is blank then go to column g only count it if column g = cell A1

  9. Hellow Svetlana Cheusheva, Forwarded the workbook file by mail for your guidance and support. Hope you can help me.
    Thanks

  10. Hi Svetlana,

    I have three columns that are populated via a pivot table (in tabular format). They are site (a), manager (b) and rep (c). Only one cell populates per row across the three columns. I'm trying to build 1 formula that can be applied to all rows in one column (d), that counts the # of items entered in a given month for either site, manager or rep...depending on which column (a-c) is populated in that row.

    I recently built the below formula that looks at the rep column first. If it is populated, it counts the # of times that reps name shows up in column b on the data tab. If it is blank, then it knows it needs to refer to column b for the manager name and then count the # times that managers name shows up on the data tab in column f.

    =IF(C6="",COUNTIF(DATA!$F$3:$F$3000,B6),COUNTIF(DATA!$B$3:$B$3000,C6))

    I need help adding an additional (nested if or and) function that says if both column b and c are blank, refer to column a and then search the # of times that site shows up in column d of the data tab. Let me know if I am not explaining this well enough. I appreciate your help in advance!

    Thanks!

    1. Hi Amber,

      I think we will be able to help you better if we can have a look at your data. I possible, please send your sample workbook at support@ablebits.com.

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

  12. Hello,
    DESCRIPTION OF FORMULA MADE IN EXCEL WORKSHEET.

    C2 WILL BE ENTERED BY USER.
    In D2 the entered value of user will be searched (LOOKUP)in other worksheet with in specified range of column (Sheet1!$A$30:$A$39) and output will be the specified range in other column (Sheet1!$B$30:$B$39).
    If there no entry in the cell (C2) by user the cell (D2) will remain Blank.

    Here my requirement:
    In addition to above condition the value entered in C2 should not be equal to Value of A2, if equal then it should be an error message (N/A).

    PARAMETERS OF THE CELLS in worksheet 2:
    A2 DUTY CODE NORMAL
    C2 DUTY CODE OVERTIME
    D2 TIMING OF DUTY

    Sheet1!$A$30:$A$39 - Worksheet 1 A30 to A39 are duty codes
    Sheet1!$B$30:$B$39 - Worksheet 1 Duty timing

    ---END---

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

  14. I have one problem in my work sheet.

    Pls any one help me.

    I have total no. of data.
    Like

    Year Total 1 2 3 4 5
    of Cell

    1993 5 136 591 888 960 1006
    1994 3 107 177 347
    1995 5 408 1238 1352 1521 1581
    3 2215 2414 2444
    1996 5 112 1093 1144 1298 1351
    1997 5 122 238 388 458 485
    4 1104 1105 1432 1479
    1998 5 77 88 464 467 574
    5 1165 1182 1190 1374 1430
    1999 5 421 480 531 844 900
    4 1913 1935 2005 2019
    2000 5 16 36 79 95 97
    5 572 574 655 656 674
    5 1209 1275 1280 1481 1507
    3 1834 1851 1875

    i want the when ever year combined that time total of cell sum.

    1. Hello Dushyant,

      I am sorry, your data posted in the comment got distorted. For us to be able to assist you better, please send your workbook to support@ablebits.com and include the result you expect to get. We'll try to help.

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

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

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

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

  19. Hi,
    I have seen this. When i feel that i have need more than i will find out. But for this time it is very helpful to me. Thank you.

  20. Hi. i have 6 sheet tabs. The 5 sheet tabs are the summary report of 5 different branches and the other sheet tab is for my summary report. All column A of 5 sheet tabs refers to different sports while column B refers to number of votes for different sports. I need to get the top 3 sports for all branches.

    For example,
    In Branch 1, the top 3 sports listed are basketball, football and volleyball with 85, 74 and 65 votes. For branch 2, the top 3 listed are tennis, basketball and badminton with 65, 54 and 32 votes and so on. How will i get the top most sports from 5 different branches that will also add the votes if for example all branches have basketball in their list? Hope you can help me with this.

    Thank you

  21. This is so useful Svetlana, and your answers to the questions here helped a lot today at work. Thanks!

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

  23. Hello,

    I want to count the number of blank cells from a specific character.
    The problem is that the range is extended with one cell, each day. Can you automatically extend that range each day?
    Also, eventially the specific character will appear again in later cells, then I want it to start the count of blank cell from this "new" specific character.

    Example:
    The letter P is in cell B2 the range is B2:G2, hence 5 blank cells. Two days after I want the range to be B2:I2 i.e. 7 blank cells.
    Another three days later the the letter P appears in cell K2 (range is now B2:L2), hence it should start the count from K2 i.e. 1 blank cell.

    Thank you
    Hope I didn't complicate the explaination?! =)

    Thank you

  24. Hi
    Never mind, I figured it out.
    thank you

  25. Hi

    I want to be able:
    If column A = certain text, then I want all the Values in Column B(same row) be summed together:

    For example:

    Profession Mark
    Nurse 5
    Doctor 2
    Careworker 2
    Doctor 6
    Doctor 7
    Nurse 8
    Nurse 5

    Nurse Doctor Careworker
    (sum) (sum) (sum)

  26. Not sure if its possible, but can you Count the Blank spaces in One Column (say Column A) if there is specific text in Column B that corresponds to the blank area. For example, I need to know how many have responded to my invitation (blanks)in column A, but have informed me they are available (Word: Available)in column B. I have tried to combine a =CountBlank with a =COUNTIF but it doesn't seem to be working for me.
    Thank you!

  27. Greetings
    Making an attendance sheet
    Want 5 T=Tardies to couny as a 1
    Ex. 28 periods a week one has 12 Tardies which means 2 absents so 112 periods a month in average so how or which formula can help me count 5 T=Tardies as 1

    Thanks in advance

  28. I need to count the number of names in a column, but exclude any that have been struck through.

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

  30. Trying to calculate the number of times certain words appear in a column. Some of the cells works others don't and I am using the exact same formulas for both. Even when I click in on the fx button the total is correct in the formula, but it won't replicate it to the cell. The cell just shows the formula an not the number. I have all the cells formatted to general, even the ones that are working correctly.

    1. Hello Lance,

      It's hard to pin down the cause of the problem without seeing your data. If you can send you sample workbook at support@ablebits.com, we'll try to figure it out.

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

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

  33. I am trying to create an auto Vacation equation template. I have completed the template and it is working well except, I would like it to do something like this.

    Example:

    =COUNTIF(D3:J20, "BBB-V") Currently the template is counting one for each one it sees, but what I would like it to is to equate 1 with a (NUMBER) like "10" to allow for actual hours employee is being deducted from balance the employee has. So if it sees one I would like it to see it is 1=10 and display the number in the cell as 10... Have I made this clear?

    1. Hello Dawn,

      I am not sure I fully understand your task. You can try one of the following solutions:

      Solution 1 – you can just multiply the result by a certain number, say 10, othe formula will be as follows:
      =COUNTIF(D3:J20, "BBB-V")*10

      If you want to pull the number from a certain cell, replace "10" with a cell reference.

      Solution 2 – if you want to show 10 for COUNTIF=1, and for the other cases you want to do something different (e.g. multiply by 9):
      =IF(COUNTIF(D3:J20, "BBB-V") = 1, 10, COUNTIF(D3:J20, "BBB-V") * 9 )

      If neither solution work for you, please send a sample workbook with your data at support@ablebits.com and we will try to help.

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

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

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

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

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

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

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

      1. I know this thread is a couple of years old, but the "Attendance/Absence" spreadsheet is *exactly* what I've been trying to create this week for my work.
        The countif formula given above would work but only for one of the Key's provided. If one has more than 2 (eg one full letter for V or B or S which needs to be counted as 1 and one which has the above with a "H" in it to be counted as 0.5), then the formula wouldn't be working for all.

        The secret is to use the wildcards. A Question mark - "?" can be used in the criteria to reference a single text character. So no matter what text character is used if it is on its own, it will be counted.
        This would be then written as:
        =Countif(RANGE,"?") and would return a value. In effect counting any cell with ONE text character in it.
        To create the half version, the formula would be:
        =Countif(RANGE,"*H") It works in the same way but is now separately counting only those entries with ANY character to start with (the "*") AND which have a H by them. This last then needs to be divided by half.

        The example above would work, but maybe a more elegant example would be to simply divide by 2. The difficulty is figuring out the syntax which in this case is where the brackets are. The answer is:
        =(COUNTIF(RANGE,"*H")/2)+(COUNTIF(RANGE,"?"))
        The first pass reads the entries with the H and then divides by 2. The entire phrase is bracketed together (usually COUNTIF doesn't have a bracket before it). Excel knows that the total its found must be divided by 2 before working with the next phrase
        The second pass then does exactly the same thing but with any single text character entered but does not divide. Instead its total is then added to the previous total.

        NOTE: the cells which will display the result MUST BE PREFORMATTED as a Number and with one decimal place. This will provide the halves when they are counted or Excel will automatically round up.

        Hope that is useful.

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

  42. I want to group or count values that start the same. In this short example YTOP.125 should be grouped together - or counted. I can work with either. So I want to be able to say "if the first, for example 8 characters are the same then...
    For example
    ColumnA
    YTOP.125SP
    HYEP.556M
    YTOP.648
    MNTK.593
    HYEP.257
    YTOP.125LO

    Can you help? Is this possible?

    1. COUNTIF(A2:A7,LEFT(A2,8)&"*")

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



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