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

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

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

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

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

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

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

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

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

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

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

  11. I have sheet 1 "South", contains a range of criteria.
    In Column A lists codes containing N or M followed by a number ie N2, M4 etc. In Column C lists New Birth, 0-11 age, 11+.

    The data above I want to both be calculated so how many New Births in N or M (any code with N "N*", are there. No mater what the number is)
    with in a specific date range ie 1/4/14-30/6/14, which I know would equal QTR 1.

    The question I want to ask are:
    How many New Births in N (Newtown) were there in QTR 1 (1/4/14-30/6/14)

    I ideally this new data I would like to be linked to a new work book, but that calculates and updates when the data changes in work book 1.

    Many thanks in advance

  12. Hi
    I have Excel File with more than 1000 columns and rows and in some rows/columns, there are email addresses. I want to collect all emails only in one column. Can you please guide

  13. Hi Svetlana,

    I am using formula =IF(K5>=$A$1, "True", "False") where A1 is 01/04/2014. Now I want to add additional criteria to it as below, please can you advise.

    I have A1= 01/01/2014 and B1=31/03/2014
    I want C1 to calculate if D1 cell has the date that is >= A1 and <=B1 then put value True otherwise false.

    Thanks in advance
    Rachana

    1. Rachana,

      You don't need the COUNTIF function for this task. Just add one more IF statement to your formula:

      =IF(D1>=$A$1, IF(D1<=$B$1, "True", "False"), "False")

      1. Thank you Svetlana!

        Thats quite helpful:)

      2. Thank you again Svetlana!!!

        Please help on another query.

        I am using a formula - =COUNTIFS('6-26-52 Weeks'!$F$4:$F$52,"=Mike",'6-26-52 Weeks'!$I$4:$I$52,"=April",'6-26-52 Weeks'!$J$4:$J$52,"=True",'6-26-52 Weeks'!$Q$4:$Q$52,"=Y")

        I needed the total count of Ys avilable in sheet 1 provided it matches the criteria that is - Col F to find the name e.g. Mike, Col I to check the month e.g. April, Col J to count True and then Count Y from Col Q.

        For some reason it doesnt give me any formula error neither it counts Y in the mastter sheet.

        Regards,
        Rachana

  14. Hi 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 in advance
    Rachana

  15. Hi Svetlana,

    I have data in Col that A.
    I want to know no of lines between each pipe (|)

    Expected Result in B col for
    first pipe is 0
    Second Pipe is 6
    Third Pipe is 7

    Data
    |
    1
    ZGF1213420
    S ILIYAAZ
    S AZEES MIAH
    34-53-22
    29
    |
    2
    ZGF0316174
    U SUMALATA
    NARASHIMHA
    MURTHI
    53/1
    21
    |

    Thanks in Advance,
    Khalid

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

  17. please help me Svetlana. I have a problem about sequence of numbers that is a lot to be able to read just one in a column. eg: column A there are numbers
    9,1,2,3,4,2,2,5,6,2,7,5,4,8,9,2,10, then I would like to read into 1,2,3,4,5,6,7 , 8,9,10. how the formula? thank you very much

    1. Hello Handri,

      I am sorry, it is not very clear what you want to get. If you can send the source data and the expected result to support@ablebits.com, our support team will try to help.

  18. thanks Svetlana,it worked and saved my almost 10 mins from daily my work :).

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

  20. Hello Svetlana,

    I'm looking to count the number of occurrences of text in column A while checking the value in column B to be >0.

    Let's say if my Text repeated for 5 days a week having value higher than 0. I need 5 in front of that text.

    Appreciate your help!
    Syed

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

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

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

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

  25. Hi Swetlana,

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

    Thanks,
    Rachana R

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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