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

  1. Hello

    I have a question regarding a spreadsheet I am putting together and I am unsure if using counts would be the best way.

    ITEM # | TRANS CODE | DOC DATE
    AA R 10/1/15
    AA R 10/1/15
    AA R 10/2/15
    AB R 10/1/15
    AB R 10/1/15
    AC R 10/2/15

    In the above example I have three part numbers, AA, AB, and AC. Each one is a receipt on the day in the last column.
    I am trying to count the number of receipt days in a week for each part, not just the number of receipts per day.
    So for the AA I would have two receipt days (10/1 and 10/2), for the AB and the AC I would have one receipt day.
    If I were counting the number of receipts would results in AA:3,AB:2,AC:1
    My problem is I can not figure out how to do the code so that way when I type in the part number it returns and then sums the number of receipt days and not the number of receipts.

    1. Hello Jess,

      I recommend adding a column that will check if the values have duplicates
      =IF(COUNTIF(B6:$B$11,B6)=1,"no duplicate","has duplicate below").

      Then you can use a Pivot Table to count filtered values.

      Please see a sample file with the functions that I described:
      https://support.ablebits.com/blog_samples/excel-countif-examples_202.xlsx

      Our Excel add-in that allows to remove duplicate rows can also be helpful for you:
      https://www.ablebits.com/excel-suite/find-remove-duplicates.php

  2. Hello,
    Example: =COUNTIF('C:\Users\Desktop\[test.xlsx]List1'!$B$1:$B$10;B11)
    Why when I close test.xlsx file this function returns #Value!# error?
    (for other functions a link is not problem)
    thank you for your answer, really :)

    1. Hello Lukas,

      Unfortunately I haven't found a way to get the formula re-calculated correctly without an open book.

  3. Hello Mr Svetlana, i'm a doctor and i'm trying to figure out how to organize my colleagues data shift. I tried to use the "countif" formulas and it worked fine for normal shift that has to count 1 (stands for 1 turn of 6,3 hours) but i would like that excel will count a night shift as 2 (because the colleague that appear in the night line one time does 2 turns because he/she remains for 12 hours. How can i do that?
    Thank you very much

    1. Hi Paolo,

      It's difficult for me to suggest an exact formula because I don't know how you identify night sifts. But you can use the following approach:

      =COUNTIF(A:A, "night shift")*2

  4. Hi!
    I have a list of approximately 12000 rows with data. Column A has unique IDs, while column B has a date. How can I remove duplicate rows based on identical ID, only when there exist another date within 60 days of the first post?
    123456 12.09.2015
    123457 13.09.2015
    123456 15.10.2015
    123458 25.11.2015
    123456 12.12.2015
    In the example above, I want to get rid of the third line as it is within the 60 days from line 1. However, I want to retain line 5, since that is more than 60 days after line 1. Is it at all possible?

    Sincerely,
    Christoffer

    1. Hello Christoffer,

      I recommend you to add a combined column in the following way:

      ID Date Combined
      123456 9/12/2015 123456 more 60
      123457 9/13/2015 123457 more 60
      123456 10/15/2015 123456 more 60
      123458 11/25/2015 123458 more 60
      123456 12/12/2015 123456 less 60

      Please see a workbook with an example here:
      https://support.ablebits.com/blog_samples/excel-countif-examples_198.xlsx

      Then you can use our Excel add-in that allows you to remove duplicate rows.
      https://www.ablebits.com/excel-suite/find-remove-duplicates.php

  5. Hi Svetlana,

    Would it be possible to count a number of cells that contain numbers with letters? such as a list of number plates?

    AV63 OEB
    AV63 OEM
    AV63 OEN
    AV63 OER
    AV63 OES
    AV64 PYH
    AV64 PYW
    BJ13 LZW
    BJ13 MGE
    BV13 CZH
    BV63 CGF
    CK13 DKJ
    FD13 KYJ

    Thanks!

  6. I'm trying to count how many "Yes" in I2:I322, only if D2:322 equals "SSA"

    =COUNTIFS(USA!D2:D322, "SSA",USA!I2:I322, "Yes")

    It's only counting a subset of all the "Yes"s that qualify.

    1. I figured out this formula is working. The problem is that it seemed it was wrong because of filtered rows. So, please ignore this question. Thanks :)

  7. Hi

    I have looked at the responses above but I can't see anything close to what I need. I have 3 columns. the first has office names and the second has PO numbers, the third has dates. The second column has unique and duplicate PO's. I would like to count the amount of uniquem then duplicate PO's from a certain office in a certain date range. i.e Cardiff between 1-2-15 and today.

    Thanks

  8. Hi Svetlana,

    Quick question on how to use the countif/countblank.

    I have an excel spreadsheet where I need to determine the number of items in a column that are blank so I am using =countblank (A1:A94) but I have to continually change the range because if I do =countblank (A:A) I will get too many results.

    So my question is which formula could I use to count blanks in all of A if column B = Yes

    1. I figured it out after browsing the site some more.
      For those wondering; here is the formula

      =COUNTIFS(A:A,"",B:B,"YES")

  9. Hello, i'm trying to count the results to a survey i conducted and i need to graph very specific things.

    What i want to do, in English, would be like:

    If (column C)= Female, count how many times the word "weapons" is in column G

    I don't know if it's there in the examples and i can't see it because i'm not used to Excel, or if it just can't be done.

    Thanks for the tutorial though! I'm going to use some other things.

  10. What do i need to do to count data that has a date less than today, but only count as far back a 2 weeks?
    =countif(A23:a,""today -14??

    1. Hi Matthew,

      To count with 2 or more conditions, you need to use the COUNTIFS function:
      =COUNTIFS(A1:A20, "<"&TODAY(), A1:A20,">="&TODAY()-14)

  11. Hello,

    I want to COUNTIF a range A:B that is less than zero and out of that I want to pick how many are from Paris?

    I can do the first part and count how many are less than zero but how do I pick from that how many are equal to Paris?

    I seem to be able to do 2 elements in a statement but cannot add a third?

    Carol.

  12. I have 10 columns (1 row) of data like below:

    A|B|C|D|E|F|G|H|I|J
    1|0|7|5|0|1|2|3|5|4

    I need to form a new colum with triplets of numbers from each row,
    like this:
    A |
    107|
    075|
    750|
    501|
    012|
    235|
    354|
    Number 107 is formed of each separate value from cels A,B,C
    Number 075 is formed of each separate value from cels B,C,D
    Number 750 is formed of each separate value from cels C,D,E
    .
    .
    .
    etc.
    Can enyone help me to do it?

  13. Hi there -

    I am looking for help.

    I need a formula that will count the number of times "Strongly Agree" appears in column F only if there is a number greater than 0 in column AF
    I tried using multiple countIF functions as well as trying a Vlookup and am hainv no luck.

  14. I have 10 columns (1 row) of data like below:

    A|B|C|D|E|F|G|H|I|J
    1|0|7|5|0|1|2|3|5|4

    I need to form a new row with triplets of numbers from each column,
    like this:
    A |
    107|
    075|
    750|
    501|
    012|
    235|
    354|
    Number 107 is formed of each separate value from cels A,B,C
    Number 075 is formed of each separate value from cels B,C,D
    Number 750 is formed of each separate value from cels C,D,E
    .
    .
    .
    etc.
    Can enyone help me to do it?

  15. Hello please am trying to play around to see if i can use COUNIF or COUNTIFS to make my formula to count P and PS as one and the same thing in my totals. Thank you so much for the rapid response

    1. Hi Silvester,

      You can add up 2 COUNTIF functions, like this:

      =COUNTIF(range, "P") + COUNTIF(range, "PS")

  16. Nice article, thanks!
    I have two columns of data like below. I need to count the number of times the value in column A is greater than the one in column B. Is this possible using COUNTIF?

    A | B
    2 | 1
    1 | 2
    3 | 0
    2 | 1

    Result A > B: 3 times
    Result B > A: 1 time

      1. Thanks, that works great! This is the formula I’m using (in Google Sheets):
        =ArrayFormula(SUM((Data!B2:B1000 > Data!C2:C1000) * 1))

  17. I've got a table of data that I have then created a number of concatenated columns from. From example, in my data, I have lists of contract numbers, and the month in which those contracts were processed (in some cases, this is across more than one month). I have therefore added a concatenated column that combines the contract number and the month number (1 for January, etc).

    I want to be able to find out the number of times each contract has occurred within each month so I have tried a simple countif formula that says =COUNTIF(A:A,C1). Column A contains the concatenated column and C1 is where I have the contract number and month number also concatenated for the countif to be based on. However, I get a zero every time even though I know that combination of contract and month number appear three times.

    Is my problem because all parts of the formula are referring to concatanated fields?

    Thanks for any help provided - it should be so simple but there must be some sort of limitation in Excel that is holding me back.

    1. I should add that the maximum length of the concatenated contract/month field is 10 digits.

  18. Hello;
    I'm looking to set up a formula that will count all values in column A, when column B has a date of 2012?
    Thank you!

    1. Hi Grace,

      If you want to sum the values in column A, then you can use a formula similar to this:
      =SUMIFS(A1:A6, B1:B6, ">=1/1/2012", B1:B6,"<=12/31/2012")

  19. Hello,

    I have a simple two column worksheet. Column one consists of names, and column two of dates those names were given a task. I need a total that calculates how many times each name was given a task on today's date.

    So each day, as the tasks are assigned and entered the total should reflect how many were given to each name that day.

    I am trying to use a countifs formula. I have B:B as the Criteria_range1, and =COUNTIF(B:B,TODAY()) as Criteria1, but this doesn't work.

    Can somebody tell me what I need to enter to make this calculation work? Any help would be greatly appreciated.

    1. Hi Douglas,

      The formula is correct and it worked perfectly on my test sheet. I can think of only reason for it not working - dates formatted as text. Is it the case?

      1. I currently have column B formatted as Date, and chose the 03/14/01 format. :(

        1. Thanks for the help Svetlana. I'm sure it is something minor I am overlooking, but I can't figure it out. I have tried formatting column B as text, date and custom but still cannot get it to work.

          1. Svetlana, thanks, that did the trick!!!! You're amazing, and this site is wonderful! Thanks again!!

  20. Hi Svetlana,

    I am using excel 2003 :(

    I am trying to count how many cells in column D contain a specific text, IF the dates in column F are greater than 6 months ago from today.

    I have tried this:

    =SUMPRODUCT((D4:D16390=D4)*(F4:F16390="<="&TODAY()-180))

    But cant seem to get it to work, just shows 0.

    Many thanks!

    1. Hi Gary,

      Try the following array formula:
      =SUM((D4:D16390=D4)*(F4:F16390<=TODAY()-180))

      Remember to press Ctrl+Shift+Enter to complete it.

      1. Hi Svetlana,

        Perfect! Thank you very much for that!

  21. Hi,
    I need to count the amount of profit-making enterprises:

    Проект* Выручка Себестоимость
    SAMA-001_ALFA 100 90
    SAMA-003_BETA 120 200
    ...

    THANK YOU

    1. Count #projects if = Выручка>Сеьестоимость

      1. Hi,

        Supposing that "Выручка" is column B and "Себестоимость" is column C, you can use the following array formula:

        =SUM(--(B2:B10>C2:C10))

        Remember to press Ctrl + Shift + Enter to enter the array formula correctly.

  22. Please help me to solve my assignment I dont know how...
    Count if
    A B C
    1. 10 20 15
    2. 5 25 4
    3. 3 6 2

    Count if
    1) 5-2
    2) 1-5
    3) 15-25

    1. Hello Nikki,

      Here you go:

      1) 5-2 =COUNTIFS($A$1:$C$3, "<="&5, $A$1:$C$3, ">="&2)

      1) 1-5 =COUNTIFS($A$1:$C$3, "<="&5, $A$1:$C$3, ">="&1)

      1) 15-25 =COUNTIFS($A$1:$C$3, "<="&25, $A$1:$C$3, ">="&15)

  23. Hi Svetlana,

    Looking for an idea for a function. I'm pulling data from one spreadsheet with account numbers assigned to different agents, and trying to compare it to the number of entries made in our sales database for these accounts. The problem is, the database entries contain the account numbers, but also contain other text/numbers as well. Any suggestions? Thank you!

    1. After tinkering around with it, I think the main point of my problem is getting the *value* function to work with a cell reference as the value, rather than a constant.

  24. Hi,

    Can you help me out in finding the hours in an column G which is >4 <6 and =6.

    time is in 4:00:00 formate and also i need to find no 4 to 6 in same column

    Thanks
    Shwetha

    1. Hi Shwetha,

      You can use the TIME function in your criteria, like this:

      Times greater than 4: =COUNTIF(G2:G100, ">"&TIME(4,0,0))

      And use analogous formulas for other calculations.

  25. Hi - I have a baseball schedule and trying to see how many times each team has a early and late game. I have used the countifs but can't get it to work.
    Away Team # Vs Home Team # Time and Diamond
    1 vs 2 715 P
    3 vs 4 845 P
    5 vs 6 715 D1
    7 vs 8 845 D2
    11 vs 12 715 D1
    9 vs 10 845 D2
    12 vs 1 715 P
    10 vs 3 845 P
    8 vs 5 715 D1
    6 vs 7 845 D2
    4 vs 9 715 D1
    2 vs 11 845 D2
    Thank you, Kendra

    1. Hi Kendra,

      And how do you determine whether it's an early and late game?

  26. Hi Miss Svetlana how i could count the total sum of all tools without using pivot example.
    Colum A. Colum B.
    Description: Total Amount:
    Tools & Equipment A 3,000.00
    Tools & Equipment B 4,000.00
    Tools & Equipment B 1,000,00
    Tools & Equipment C 2,500,00
    Tools & Equipment D 500,00
    Tools & Equipment C 1,500.00
    Tools & Equipment E 800.00
    Tools & Equipment A 3,800.00
    Tools & Equipment E 540.00
    Tools & Equipment E 900.00
    Tools & Equipment E 100.00
    Tools & Equipment E 100.00
    Tools & Equipment E 300.00
    Tools & Equipment B 6,000.00

    Can u help me pls.......

    1. i need to total by each tools without using pivot

  27. Hi Svetlana,
    I am using COUNTIF to give me a count of results that are in a range. I want to know how many fall between 8,000 to 9,000, 9,000 to 10,000, and so on. My range of data to look at is A1-A164 but within that range are many subcategories with totals for that category. My COUNTIF counts the total in the results but I don't want the totals counted. Other than making 50 separate ranges is there any other way you can think of to exclude the total cells from being included in the count?

    Thank you for any help you can offer.

    1. Debbie,
      I realize it has been some time since if you inquired; my apologies. If I'm understanding your dilemma properly, is it not possible to use COUNTIFS and ignore rows where the total values have the keyword "Total" in an adjacent column?

    2. Hi Debbie,

      I am afraid I cannot figure out any way other than you suggested :(

  28. Hi Svetlana,

    Can you help please?

    I am currently using the CountA function to count the cells in a column.
    Some cells have ? or * in them.
    I have the formula to not count these cells?
    How do I achieve this?

    Thanks much.

    1. Hi!

      You can count the cells with ? and * and then subtract them from the total count, like this:

      =COUNTA(A1:A100)-COUNTIF(A1:A100,"~*")-COUNTIF(A1:A100,"~?")

  29. Hi,

    I was wondering if it is possible to count partial matches in the following example:
    Orange juice
    Apple juice
    Juice
    Coca Cola

    So would want the count if formula return 3. I believe if I use *juice it will count only the first two, as "Juice" doesn't have anything in front of it.

    Thanks!

    1. Hi Lidiya,

      You can use *Juice* and it will count all 3. The asterisk tells the formula to count all instances regardless of the presence or absence of any other characters in front of / after the word.

  30. Hi,

    Is there any way we can figure out a formulae for this tracker. Basically tracker should automatically calculate the blood samples drawn 12 months from the day they signed the consent form.

    Eg- if patient signed consent in 27/07/2013 and the bloods are drawn until end of JULY 2014, the tracker should calculate signed consent + 365 days.

    Thanks a lot in advance.

    1. Hi Sat,

      I think it is possible. For me to be able to suggest a formula, please explain the structure of your data, i.e. what columns contain signed consent dates and blood drown dates and probably patients' names. Also, what exactly you want to get as a result - the expiry date for each blood sample or something different?

  31. Hi Svetlana,

    Thank you for this very helpful topic!

    I have this question: Is it possible with a formula to count all the rows from A to D in which the sum of the cells is greater than 0 and between 4.

    For example in this case, the result will be 2 :

    A B C D E
    1 0 1 5 0 4
    2 0 3 12 0 0
    3 0 1 2 0 1
    4 7 2 5 0 2
    5 0 0 0 1 4

    Thank you very much,

    Elena

    1. Hi Elena,

      Sorry, I am a bit confused. Do you mean the sum of values in columns A through D in each row, e.g. A1:D1, A2:D2, etc.? If so, it is always greater than 0 and even greater than 4 in your example. Please clarify.

  32. how to count a data by date but including text contain like

    A columns
    1-Mar-15
    6-Mar-15
    11-Mar-15
    16-Mar-15
    21-Mar-15
    26-Mar-15
    31-Mar-15
    a
    a

    how do i take all the count

    1. Hi Shoaib,

      Not sure if I understand the task correctly. Anyway, if you want to count all cells with any data in column A, you can use the following formula:
      =COUNTIF(A2:A100,"<>"&"")

      Please see the example in the "Count if blank or not blank" for full details.

  33. Hi
    I want to know whether it is possible to get the count of cells that are not empty.

  34. Hello i am having a Count if issue

    the formula i am using is:
    =OFFSET('Raw Data'!$K$5,1,0,COUNTIF('Raw Data'!$K:$K,">0"))

    i am using this in name manager to automatically update a control chart as i dump data in my "Raw Data" tab.

    the chart is a representation of a shipments date (x axis) and the analysis of the shipment on that date.

    the issue that i am having is that the formula is not counting the entire column. Furthermore my chart is still picking up the zeros

    there is a large amount of entries in the column (J5-J65 the formula above stops at J26. i can not figure out why.

    1. Im sorry

      in the range above i mean K5-K65 and stops a K26

  35. hi Svetlana Cheusheva..

    I think you are a MVP.

    I had gone through the your website and found that you have helped alot of people in solving excel problems. .

    I really appreciate your efforts.

    I want to be excel expert like you tell me what should I do for that...

    God bless you..

    1. Hello Bharat,

      Thank you very much for your kind words. No, I am not an MVP, but I am lucky to work with very talented and knowledgeable people and I've learned a lot from them. Reading also helps : )

  36. I have the exact same problem as Donna (Q95). Essentially, I'm trying to get a single total of the number of projects where the actual completion date (column B, for example) exceeds the proposed completion date (column A). I’ve tried various formulas, including the one Donna noted, with no success. Any help would be greatly appreciated.

  37. Hi I am trying to count the number of clients within ranges.
    example:
    all clients who are >= 10 but =10")and=COUNTIF(H3:H1659,"20")

  38. Hi Svetlana,
    Thank you for your informative post and support! I dates in 2 columns: one column (M) for "original planned date of project completion", and another column (N) with "latest planned date of project completion". I want to count the projects that have a latest date later than the original planned date, i.e. "count if the date in column M is greater than the date in column N" specifically trying this formula COUNTIF(M3:M22,">L3:L22"). It is not calculating correctly - any ideas?
    Thanks again!!!

    1. Hi Donna,

      The COUNTIF formula cannot help in this case because you need to compare 2 ranges. Try the following array formula (remember to press Ctrl+Shift+Enter) to enter it correctly:

      =SUM((M3:M22>L3:L22)*1)

  39. I'm trying to count non duplicate using
    =SUMPRODUCT((COUNTIF(L13:L567,L13:L567)=1)*(L13:L567""))

    but when I filter other column to unique name, the total remaining the same. How it will be possible by using same formula? Thanks.

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

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

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

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

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

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

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

    Example (cells N2:N9):

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

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

    Thanks

    Matt

    1. Hello Matt,

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

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

  47. Hi there-
    I 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")

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

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

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

Post a comment



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