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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    thanks Liam

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  45. Hi Swetlana,

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

    Thanks,
    Rachana R

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

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

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

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

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

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