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 2. Total comments: 1067

  1. Hi Alexander,
    -First of all thanks for your article, I've using myself this Countif Function for a while and I believe it's super useful!
    -Secondly, I have tried to recreate the situation below (I hope the formatting is ok, otherwise, i have created some screenshots which I could send to you, which will probably explain better my Problem, but I cannot add the on this Forum, but I will try to summarize my problem below--> i have added on the "table" below on the Top, the Column Names and on the Right side, the Row Numbers).

    -Problem Description:
    ->I have the Following Sections on the table:
    --> 1. Tasks and Allocations of Group of People (aa, bb, cc) and People (o, p, q, r, s, t, u, v)
    --> 2. Total tasks per weeks
    --> 3. Group of People (aa, bb, cc) and People (o, p, q, r, s, t, u, v)
    -> The Inputs from B2:F12, I input manually
    ->By using the following Formula, I can count how many tasks are planned weekly (Results on Row 13: =SUBTOTAL(109;B2:B12) ) --> This works perfectly
    ->By using the following Formula on the array B14:F24 (=COUNTIF(B1:B12;"*"&INDIRECT(ADDRESS(ROW();1))&"*") ), I can know how many times the Groups of People (aa, bb, cc) and the people (o, p, q, r, s, t, u, v) have been allocated each week --> This is working fine as well and these numbers are the results you see in between B14:F24
    ->Now here comes my "Problem" where I am unfortunately going nowhere, is there a way that I can multiply the results of B14:F24 by the values of the tasks, on the weeks where the group of People or People have been allocated? So a Concrete Example: D17 has currently a value of 2, because through the function (=COUNTIF(D1:D12;"*"&INDIRECT(ADDRESS(ROW();1))&"*") ), it found on array D1:D12 the value "bb" twice. What I'd actually want, would be that everytime the value "bb" is found, it multiplies it by the value given on the Tasks Row (Task a, Task b, Task c), so in this case, to multiply it by D2 (Value=0,5, which is the weight Value of "Task a" on that week) and by D5 (Value=1, which is the values of "Task b" on that week). This will give me in return on D17 an added Value of 1,5 instead of 2.

    I hope my explanation is understandable, please let me know if you need any more info from me. I'd really appreciate you support. Thanks in advance.

    Regards,

    Vini

    Example:

    A B C D E F

    CW1 CW2 CW3 CW4 CW5 1
    Task a 1 1 0,5 0,3 1 2
    aa / o aa / o bb / q aa / o 3
    aa / p aa / p aa / p 4
    Task b 1 1 1 5
    bb / q bb / q 6
    bb / r bb / r 7
    Task c 1 1 1 1 1 8
    cc / s cc / s 9
    cc / t 10
    cc / u 11
    cc / v cc / v 12
    3 2 2,5 2,3 2 13 ---> Tasks sum
    aa 1 1 1 1 2 14
    o 1 1 0 0 1 15
    p 0 0 1 1 1 16
    bb 2 0 2 1 0 17
    q 1 0 2 0 0 18
    r 1 0 0 1 0 19
    cc 1 2 1 1 1 20
    s 1 1 0 0 0 21
    t 0 1 0 0 0 22
    u 0 0 1 0 0 23
    v 0 0 0 1 1 24

    1. Hi! I don't really understand how you get a result of 1.5 in your example.
      I can suggest an alternative to your formula in D17:

      =SUMPRODUCT(--ISNUMBER(SEARCH(INDIRECT(ADDRESS(ROW(),1)),D1:D12)))

      This formula creates an array of 0 and 1, where 1 corresponds to the cell where the match is found. For more information, please read: How to find substring in Excel.
      You can multiply each value of this array by a specific value, such as D2:

      =SUMPRODUCT(--ISNUMBER(SEARCH(INDIRECT(ADDRESS(ROW(),1)),D1:D12))*$D$2)

      I hope this will help.

      1. Hi,
        Thanks for your super quick reply, I really appreciate it. it is a bit difficult to expain it, because the formatting of the Table I put on my previous comment changed once I posted it :(. Any chance I can send you an Email somewhere so I send you my Screenshots and maybe you have some ideas about it?

        If not, I will try to explain as best as I can, I wouldn't like to use fixed values (Like $D$2),because I have a very large table, with around 1000 rows and like good 500 Columns on the Manual input Areas, so I cannot really fix the values, because otherwise I cannot populate properly the "Countif" Function, because I would be pulating always the same fixed value, which would not help me.

        I came up with an idea though, the Function that I have, works great already as a counter and I believe I could keep using it. =COUNTIF(B1:B7;"*"&INDIRECT(ADDRESS(ROW();1))&"*") ----> Is there any way I can use CountIf or any similar function as to get partial information inside of the cells where countif counted? --> I'd like to sum the values (last 3 digits of each cell from right to left) everytime for example "x" is found and then multiply this summed value of all cells where x was found by the counter I have already setup (This one: --> =COUNTIF(B1:B7;"*"&INDIRECT(ADDRESS(ROW();1))&"*") ) --> I prepared a new example below. Let's hope this time the formatting works...

        A B
        - a
        - aa / x / 0,1
        - aa / y / 0,1
        - bb / z / 0,1
        - b
        - aa / x / 0,3
        - aa / y / 0,1

        aa -
        x =COUNTIF(B1:B7;"*"&INDIRECT(ADDRESS(ROW();1))&"*") multiplied by the summed values of all cells after "/", everytime x was found
        y -
        bb -
        z -

        I really appreciate your support. Thanks a lot!

        Regards,

        Vini

        1. Hi! I don't really understand what result you wanted to get from your data.
          This formula sums all numbers in the cell range that are written after "x".

          =SUMPRODUCT(IFERROR(MID(A1:A10,SEARCH("x",A1)+3,10) * ISNUMBER(SEARCH("x",A1:A10)),0))

          1. Hi Alex,

            Wow this is great!!!

            It worked almost perfectly with just one little hiccup.

            The function you provided will only sum the values, if the value that needs to be found "x" is available first on A1, because of the following criteria on your formula (SEARCH("x",A1). If the value is only available after, for example A2, then it unfortunately does not sum all the values after "x" on the range I have given and it returns a 0 :( ... Is there a way to change this? Because I would never know if the value is first Available on A1, A2, A3, .... or any other cell, within the Range I am looking at.

            Once again, a thousand thanks for your support!

            Regards,

            Vini

            1. Hi! As I said, I tried to guess what you wanted to do. Unfortunately, you have not explained to me what kind of result you want. Your comments are hard to understand.

              1. Hi Alexander,

                I want to say thank you for your patience and the effort you put onto coming with this Formula. It is exactly what I was looking for. So thanks a lot!!!

                As, I mentioned on my previous comments, I wanted to have keep the value to be searched, as variable (in your example "Group 1"), so I did a little twitch to your Formula and it works perfectly! In case it is interesting for you, the new formula looks like this: =SUMPRODUCT(ISNUMBER(SEARCH("*"&INDIRECT(ADDRESS(ROW();1))&"*";B1:B14))*IFERROR(VALUE(RIGHT(B1:B14;LEN(B1:B14)-SEARCH("#";SUBSTITUTE(B1:B14;"/";"#";2))-1));0))

                Once again, thanks a lot, it was a great exchange, have a great week.

                Regards,

                Vini

              3. Well, here we go again :), let's hope my example helps. For better understanding, I am doing it with only 2 columns and the separator in between the Columns is "\" and the Symbol "-" means that the cell is empty; Symbol "-->" Used within a statement just to clarify any point on each specific cell:

                Column A \ Column B
                - \ CW1
                Project A \ -
                - \ Group 1 / Person 1 / 0,3
                - \ -
                Project B \ -
                - \ Group 1 / Person 1 / 0,3
                - \ Group 2 / Person 3 / 1,0
                Project C\ -
                - \ Group 3 / Person 5 / 0,7
                -\ -
                - \ -
                -\ -
                - \ -
                Group 1 \ This should be 0,6 --> Because the "Group 1" has been allocated 2 times this week on Projects A and B, and on each allocation they have a value of 0,3
                Person 1 \ This should be 0,6 --> Because the "Person 1" has been allocated 2 times this week on Projects A and B, and on each allocation they have a value of 0,3
                Person 2 \ This should be 0, as it has this Variable has not been allocated this week
                Group 2 \ This should be 1 --> Because the "Group 2" has been allocated 1 time this week on Project B, with an allocation with value of 1,0
                Person 3 \ This should be 1 --> Because the "Person 3" has been allocated 1 time this week on Project B, with an allocation with value of 1,0
                Person 4 \ This should be 0, as it has this Variable has not been allocated this week
                Group 3 \ This should be 0,7 --> Because the "Group 3" has been allocated 1 time this week on Project C, with an allocation with value of 0,7
                Person 5 \ This should be 0,7 --> Because the "Person 5" has been allocated 1 time this week on Project C, with an allocation with value of 0,7
                Person 6 \ This should be 0, as it has this Variable has not been allocated this week
                Person 7 \ This should be 0, as it has this Variable has not been allocated this week
                Person 8 \ This should be 0, as it has this Variable has not been allocated this week

                Let me know if it is now clear?
                Thanks.

              4. Hi! If I understand your task correctly, try the following formula:

                =SUMPRODUCT(ISNUMBER(SEARCH("Group 1",B2:B15)) * IFERROR(VALUE(RIGHT(B2:B15,LEN(B2:B15) - SEARCH("#",SUBSTITUTE(B2:B15,"/","#",2))-1)),0))

              5. Sorry the Thread before was so long, that I just noticed a small mistake... On 3.2., I meant "on Column B" and not "on Row B". i hope it is clear now.

                Regards,

                Vini

              6. Hi! You have given a very detailed explanation. However, in order to provide you with a formula, I need an example of the data in the column and the result you want from that data.

              7. Hi Alexander,

                Sorry, the request is a bit hard for me to explain without any pictures, but I will try to do my best now. It might be a bit long, but I hope that you can understand me.

                What I am trying to do, is:
                1. I Have basically 2 tables (Table 1 and Table 2), one above, one below.

                2. The table above (Table 1) is where I will add all the manual Inputs.
                2.a. The table has as columns Calendar Weeks from roughly Column E - FG, and Multiple Projects on the Rows (Projects A - Project Z as example)
                2.b. So This table is the database where I am going to be inputting information and from where I am going to be extracting Information for the next Table (Table 2).
                2.c. For understanding what I am doing with this table, I am planning the capacity of colleagues per week for the next 2 years in multidisciplinary projects. So my intention is basically to manually input on each cell (when needed, if not needed the cells will be empty), the allocation of my colleagues. In this way, every project Manager of each project, plans its resources weekly for the next 2 years.
                2.d. When the project Manager of any Project plans its resources on each cell, I want them to add on each cell the following information: "Group of persons" & " " & "/" & "Person" & " " & "/" & "Capacity of the person per week (Values from 0,0 to 1,0, as to reflect the percentage that this person will be allocated to this task in this week"

                3. Now, about the second Table below (Table 2), this will be the table for my capacity planning which will take information from the Table above (Table 1). How it actually Works:
                3.1. On Column A, i have added "groups of person", this will be different company teams or subsuppliers that will have specific workers working for each Group --> I have roughly 10 "Groups of persons". These amount might change in the future
                3.2. On Row B, I have added "persons" which are basically direct under each "Group of person" where they belong to. --> I have initially created roughly 10 persons per group (as starting point)
                3.3. Now, the main result I am after and what I want the table below to do, is if you are on any cell on this table below (Table 2), to basically read the values on the Column "A" or Column "B" on the same row from the cell where you are at, which are going to be either the "Groups of Person" or the "Persons" and count how many times they have been allocated across the multiple projects on the table above on that specific week and with which capacity they have been allocated on each Project and add them together (this will be basically the values in between 0,0 - 1,0, mentioned on 2.d, which will be added after the second "/" at the end of the entry on each cell of the table above). In this case if for Example, "Person Nr. 1" has been allocated with 0,3 on Project A, with 0,5 on Project B, with 0,0 on Project C, with 0,0 on Project D and with 1,0 on Project E, I will know that the total allocation of the Person is 1,8 on that specific week and this will be reflected on the cell where I am standing.
                3.4. As you have different rows for "Groups of Persons" and "Persons", and because the cells on Table 1 have been configured with this information (See 2.d), it will depend on the row you are on, if the counter will count the allocation (sum of values from 0,0 to 1,0) of "Groups of persons" or "Persons"
                3.5. What I do already have managed to do and how this whole thread started, is to do a very simple countif, where I basically count how many times the "Group of Persons" or "Person" have been allocated across the multiple Projects during a specific week. Hence, the Formulas:
                3.5.1. Example of Formula for Rows on Table 2 where I have "Group of Persons": =COUNTIF(N8:N679;"*"&INDIRECT(ADDRESS(ROW();1))&"*")
                3.5.2. Example of Formula for Rows on Table 2 where I have "Persons": =COUNTIF(N8:N679;"*"&INDIRECT(ADDRESS(ROW();2))&"*")

                As a Summary, the Task I am after is the one described in 3.3.

                I hope this helps and you can help me further with my very particular situation.

                ***As closing statement: As I tried to explain on my previous comment, the formula you came with kinda helped, but unfortunately you don't know where exactly on the whole array of each week (Example N8: N679), your value is going to be found (For Example: "Person 1"), and if you do not know this, and you cannot reference a cell where this value is, then you only receive in return a value of 0***

                Once again, thanks again for the support. I'd understand as well, if it is too much what I am asking for, and you need to stop this thread. But if you come with any idea, I will highly appreciate it! Thanks in advance!

                Regards,

                Vini

            2. Sorry, I thought about something after writing my comment...

              Like, is there a way to get the Cell Number, where you first find the value you are looking for within a Range? In this way, I could replace this new Formula by A1... Just as an idea

              Once again, Thanks.

        2. Btw, "-" in my example above, are empty cells, just to give it more of a table character...

  2. Great article thank you

  3. I wanted to count the cell used displayed in pivot by headcount, whereas the (Headcount is 15) while (Headcount 2) is 4. See example below

    Row Labels
    (+/-) Headcount
    1
    2
    3
    ...
    15
    (+/-) Headcount 2
    1
    2
    ...
    4

  4. Hello,

    My issue is creating a count function for 3 criteria. My workbook has 12 columns of information and I am trying to create counting functions meeting filter options. For example, if I want to see how many individuals I have with a specific class license (column a) [what I am looking for] from a specific department (column b) [filter option], and years of service greater than 5 years [filter option], how would I construct that function?

  5. Hi.

    I'm looking to make a formula that the result would show whether or not a person (inspector) with a particular qualification conducted an inspection within a date range.

    For this scenario, we would have a column for the inspection date and a column for the qualification of that inspector as shown below.

    Inspection Date| Inspector Qualification
    01/12/24 | Insp.
    01/19/24 | QSP
    01/25/24 | QSD
    etc...

    The result would show a "0" or "1" (or preferably "Yes" or "No") if a QSD had conducted an inspection from January through March (between 01/01/2024 & 03/31/2024) of the current year. Preferably this would reset when 2025 comes around, but if not I can obviously change the dates of the formula.

    And...

    Same columns as above, another formula that would show whether or not a QSP conducted a inspection within the current month.

    The result would show a "0" or "1" (or preferably "Yes" or "No") if the QSP conducted an inspection within Jan, and then the result would reset in February.

    1. Hi! To determine how many records in the table match the criteria, you can use the SUMPRODUCT function. In the formula below, you can use a reference to a cell with a date instead of the DATE function.

      =IF(SUMPRODUCT((A1:A10>=DATE(2024,1,1))*(A1:A10<=DATE(2024,3,31))*(B1:B10="QSR"))>0,"Yes","No")

      You can also use the COUNTIFS function. You can also find useful information in the following paragraph of the article above: COUNTIF formulas for dates.

      =IF(COUNTIFS(A1:A10,">="&DATE(2024,1,1),A1:A10,"<="&DATE(2024,3,31),B1:B10,"QSR")>0,"Yes","No")

  6. I have a spreadsheet with a column of number of units per order and a column of dates. There are multiple entries per date. I want to sum how many units were produced on each date.

    1 06/01/23
    1 06/01/23
    4 06/01/23
    1 06/01/23
    1 06/02/23
    3 06/02/23
    4 06/03/23
    1 06/04/23
    5 06/04/23
    2 06/05/23
    Continues. How do I get an output that summaries similar to:
    06/01/23 = 6
    06/02/23 = 4
    06/03/23 = 4
    06/04/23 = 6
    06/05/23 = 2

    1. Hi! Pay attention to the following paragraph of the article above: Using Excel COUNTIF function with dates. It covers your case completely.

    2. Additionally, I would like to use the master data tab as the source and have the output on a separate tab.

  7. I am trying to make a spreadsheet that counts the qty of parts delivered in a master sheet based on what part is delivered. I have over 300 parts that need counted. How can you easily create all 300+ criteria without having to manually input each criteria?

    1. Hi! Your question is not entirely clear. However, the COUNTIF or SUMPRODUCT formula cannot calculate the number of parts for all items at once and provide you with a list. You can get a list of all parts by using the UNIQUE function. In the adjacent column for each item you can calculate the quantity as described in the article above. For a more precise answer, please describe the problem in more detail so that I can understand what you want to do.

  8. I just want to say how much I love ablebits and think yours is the best of all excel instructions available online.

  9. Have been trying to figure out how to get a sum of unique numbers (Column C) from the following example:

    Column C
    List of over 2000 Phone Numbers

    Column D
    Dates (example rows 2-52 are one date, rows 53 - 105 a different date and so on)

    Column F
    Date lookup value.

    What I'm trying to figure out is the unique sum of numbers in column C with a date in column D that matches the date in Column F Row 2.

      1. I've tried that but I get back "That function isn't valid" and then it highlights the word FILTER. I'm using excel 2016.

  10. I have a question.

    A2:A15 contain some dates. I want to count how many dates in this range falls between each specific date range.
    C2:C9 contain a list of start dates
    D2:D9 contain a list of end dates

    This formula works (as you described above):
    =COUNTIF($A$2:$A$15,">"&C2)-COUNTIF($A$2:$A$15,">"&D2)

    But why does this formula not work? Considering that excel reads the datevalue?
    =COUNTIF($A$2:$A$15,AND(">"&C2,"<"&D2)

    2. Oh I noticed that I should use COUNTIFS. Thank you!

  11. I'm using the below to try to count the number of entries within November in a spreadsheet based on the date input into column A:
    =COUNTIF(Sheet1!A:A,">=01/11/2023"&"<=30/11/2023")

    There are 3 entries when i look manually but this produces the number 1 which is wrong. What is wrong with my formula?

  12. If i have with variouse name of differene employee and there check in time, how can count there late check in with bench mark of checkin time

  13. I am working on a spreadsheet, trying to figure out the number of clients sales. I have two columns with the year and number of times each client has ordered.

    I am a bit stumped on the formula. I would like to count how many people have ordered last year and this year. If Column B and Column C both have values and have a total of how many people have ordered both last year and this year. I have been playing with the formulas below but I can't seem to crack it. Are you able to provide any guidance ?

    1. Hi! I don't have an example of your data, and I don't know what formulas you tried using. Based on your question, here is an example of formula:

      =COUNTIF(A1:A20,"2022")

  14. Is possible to count merged cells containing a specific criteria by using count if? For example, 5 cells merged into one containing the value "abcd". Bu using the COUNTIF formula, my result is 1 but I would like to have 5 because they are merged.
    Please let me know if there any way to do it, your help would be much appreciated.
    Best Regards,
    Daniel

    1. I'm really sorry, looks like this is not possible with the standard Excel options. Also, if you split these cells, only one of them will contain a value.

  15. I work on a spread sheet where we make contact by dates with 3 different columns. First Attempt, Second Attempt, Final Attempt. I am trying to find a formula that will add up the total number of attempts by each date for each column. I have used =COUNTIFS(J2:J81,”>9000”) and I get the total attempts for that row but not the total for each individual date.
    Is that a possibility?
    Thank you kindly,
    Felicia

    1. Hi! Pay attention to the following paragraph of the article above: Using Excel COUNTIF function with dates. You can also use these methods in the COUNTIFS formula to calculate values for multiple conditions.

  16. How do I count the number of formula RESULTS that return a specified result.
    The formula is structured if-then-else =IF($A4="","",$B4)
    I want to count the number of cells that return the "else" value of B4 and not count the "" result if the first criterion is met.
    The actual formula is more complicated; thus I cannot simply count the non-blank values in col A

    1. Hi! Your formula returns the value from column B for non-blank values from column A. I would suggest counting non-blank values using the COUNTIF function as described in the article above. I don't know why you don't want to do this. Without seeing your data, I can't recommend another alternative.

  17. Hi I need to count the number of dates that is in the past. However the dates to be counted is in every second column. A will be the test date, B will be the expiry date, C will be another test date and D the expiry date for C. Thus I need to count the dates expired in A, C, E, G ect. Help would be greatly appreciated.

    1. Hi! The COUNTIF function count values by condition in only one column. Therefore, write a specific COUNTIF function for each column.
      If I understand your task correctly, try the following formula:

      =COUNTIF(B2:B10,"<"&TODAY()) + COUNTIF(D2:D10,"<"&TODAY())

  18. Your concern is appreciated,
    What does the "@" sign in the format cells mean?
    I know that "#" stands for a number
    I know that "$" stands for a Currency
    But also what sign or symbol is used for a text format?

      1. Thank you!

  19. Hi, I'm looking for a formula with several conditions

    I have an excel master sheet where I need to create month on month graphs with over 2000 records
    Column A
    Reference numbers not all Unique
    Column B
    Creation Date (Contain Dates, no blanks)
    Column V
    Resolve date (contain dates and blanks)

    I have been trying to get the unique count based on below conditions

    2. First condition Count of anything that has a Creation date before 31/01/2023
    3. Then include data where Resolve date is after 31/01/2023 and all blanks
    4. Once i have that number I Need to have the count of all unique reference numbers from Column A

    Your help would be much appreciated
    Many Thanks

  20. I am trying to do a monthly resource prediction so want to keep the month start /end date- either as a formula or cell reference. My formula is not throwing any error but not showing the result

    While this one works
    =COUNTIFS(F1:F116,"OFFSHORE",Roster!$I$1:$I$116,"BAU",I1:116, "INFRA",H1:H116,">=01-sep-2023")

    when i put the date 01-sep-2023 as a cell and refer to that in the formula
    =COUNTIFS(F1:F116,"OFFSHORE",Roster!$I$1:$I$116,"BAU",I1:116, "INFRA",H1:H116,">="&B1)

    while it is not throwing any error but it is not fetching either

  21. I am trying to get a count of cells based off of multiple conditions including a date reference from a different cell. I had a conditional format changing the cells colors based on the date but couldn't get a count from the conditional format. After trying a few VBA's that I found no luck. I then thought maybe I could combine my conditional format formula with my COUNTIF formula but it's still not working. My formulas are =COUNTIF(C6:AG6, "A")+COUNTIF(C6:AG6, "NCNS") and for the conditional one its =C5<=TODAY()-180. Is there a way I can get a count using the two formulas together? Any help is greatly appreciated. Thank you

    1. If you want to calculate values only if the condition with cell C5 is TRUE, you can combine the two formulas into one using multiplication:

      =(COUNTIF(C6:AG6, "A")+COUNTIF(C6:AG6, "NCNS")) * (--(C5<=TODAY()-180))

  22. Hi, I have a long column with dates of the form: month/day/year. How can I count the number of times one particular year shows up in the column?

  23. Hi,

    I would like to ask if there's a formula that meet the following criteria.
    a. Unique Order ID will be counted by day (e.g. Mon-Fri) in a week (e.g. weeknum 1-52) from yesterday's date after 10 am until before 10 am the next day
    b. and the Orders created from Fri after 10 am, Sat and Sun will be added to Mon orders before 10 am

    Really appreciate your help.
    Thank you.

    Order ID Order Date Order Time WeekDay WeekNum
    A 1-Jan-23 3:45:11 Sun 1
    B 1-Jan-23 3:45:08 Sun 1
    C 1-Jan-23 20:45:11 Sun 1
    D 1-Jan-23 3:45:11 Sun 1
    E 1-Jan-23 3:45:10 Sun 1
    F 1-Jan-23 18:46:15 Sun 1
    O 2-Jan-23 18:45:32 Mon 1
    P 2-Jan-23 8:45:12 Mon 1
    Q 2-Jan-23 13:45:42 Mon 1
    R 2-Jan-23 9:46:39 Mon 1
    S 2-Jan-23 2:45:23 Mon 1
    T 2-Jan-23 10:45:58 Mon 1

    how many orders drop every day based on the criteria
    WeekNum Mon Tue Wed Thu Fri
    1
    2
    3
    4
    5
    6
    7
    8

  24. 25.06.2023 aa
    26.06.2023 bb
    27.06.2023
    28.06.2023 cc
    29.06.2023
    30.06.2023
    01.07.2023 dd
    02.07.2023

    I want to count, how many in June and July ? The answer is June = 3 and July = 1

      1. Thank you very much.

  25. Nama buah|jumlah
    -----------------------------
    apel | 5
    anggur | 3
    apel | 4

    nah saya mau mencari jumlah apel di kolom a2-a4. namun di kalikan dulu apet tadi dengan angka di kolom b2-b4.
    perhitungan manualnya begini.

    (a2*ba)+(a4*b4)

    nah. begitu rumusnya giman ya?
    Makasih sebelumnya.

  26. I want to count a range of numbers, however, some of the number have a + in front of the integer and some don't. For example, +2.5 and +7. In order to get the "+" I used the apostrophe. EG: '+3. Can I now count a range of any numbers between +3 and +7? I assume that using the apostrophe turns it to text and there is no way to still count without asking Excel to COUNTIF '+1 and COUNTIF '+2, etc., etc.?

    Thanks.

  27. Hi,

    I need help with a formula, i am wanting to count columns that have a value greater than 0 but only if the date is a weekday, The colums are labeled by date only.

    I am currently using this formula which counts any cell including weekend days.

    =COUNTIF(D2:AL2,">0")

  28. I have spent many hours on here. I want to count how many, say appointments a person has in a given month. I'm not even to going to try to factor in asking to also include initials of people, that's clearly too much to ask. Trying the following:

    =COUNTIF(O2:O2974, ">=01/07/2023")-COUNTIF(O2:O2974, "<31/07/2023") As in the range from 01/07/2023 to the end of that month. Getting a 'zero'. Any help much appreciated thanks.

    1. Hi! Use ">31/07/2023" in your formula. Pay attention to the following paragraph of the article above: Using Excel COUNTIF function with dates.

  29. Hi,
    I'm strugling with very simple formula:
    =countif($a$1:a1,a1)
    I just want to count and to number in a2 all unique and duplicate values.
    Formula works perfect for static column (a1), but once I start to insert new data in column a1 - I get an error.
    Thank you in advance.

    1. Hi! I don't really understand what you want to do. A1 is a cell, not a column. Explain how you want to number unique values and duplicates. Look carefully at the examples above. I kindly ask you to take a closer look at the following paragraph of the article above: Count duplicates and unique values.

      1. Hi,
        I have One item duplicated in two rows, second item duplicated item in three rows, third item duplicated in four rows. Total three different items in 9 rows; how can I get the total number of each specific duplicated items using excel formula?. The answer should be 3 (Apple, Orange, Lemon).
        e.g.
        Apple
        Apple
        Orange
        Orange
        Orange
        Lemon
        Lemon
        Lemon
        Lemon

  30. I am trying to create a formula where excel looks for a specific name (down column A) on a separate page in the workbook & then looks across when it finds the name to count how many times they picked that week. I can’t get any formula to work I figured Countifs would do the trick. However, I keep getting an error.

    I tried for example =countifs(!’Sunday Schedule’A2:A20, “John Doe”, !’Sunday Schedule’B2:P25, “PICK”).

  31. Sir Want to count amount against the specific date (mention in column a) while amount is enter in column b, can you help me?

    Example : Column a containing on date (1-30 each Month)
    Column b containing on amount received in different sights, know i want to count whole amount which received on the same date,

  32. I'm trying to get a count of items that were completed after their due date. So in the below, Column G shows the due date, and then when the item is completed we enter the completion date into Column I. So I was trying to do a formula that would just compare if the date in Column G is less than its counterpart in Column I. Is there any way to do this without creating a new column with a =IF($G1<$I1,"Paste Due","On Time") and then counting that column?

    Column G Column I
    Due Date Completed Date
    1/1/23 1/1/23
    1/10/23 1/15/23
    1/31/23
    2/5/23 2/3/23

    I tried
    =COUNTIF(G:G<I:I)
    but that gives me a "#SPILL!" error in my formula cell.

    1. Hi!
      The formula below will do the trick for you:

      =SUM(--(G1:G100<I1:I100))

      Double minus converts TRUE and FALSE to 1 and 0.

  33. I want to make a count per day for a type of task. this would be a running total throughout a week Sunday through Saturday.
    If i select PLP in Cell D and it occurs on a Sunday which is in Cell C, then i want a running count on my summary sheet to show 1 for Sunday, Since this task is done up to 8 times daily I want to know how many throughout the day per day for the week. Appreciate any incite.

    1. I got it finally, it would let me delete, Thank you
      =COUNTIFS('Audit Tracker'!D$2:D$300, "PLP", 'Audit Tracker'!C$2:C$300, "Sunday")

  34. I am trying to develop a statement which will count the number of cells in Col A which meet ALL 3 of the following criteria:

    - the Year in the cell in Col A must = Today's Year
    - the Month in the cell in Col A must = Today's Month
    - the Day in the cell in Col A must >= Today's Day

    The data beginning in Col A1 is as follows (all of the dates are in date-defined cells and there may be some cells with text (such as "Date" in A1 or left blank such as in A6):

    Date
    05/10/23
    06/15/23
    09/15/23
    12/15/24

    07/15/23
    10/15/23
    01/15/23
    05/15/23

    The formula I created is:

    =COUNT(IF(AND(YEAR(A:A)=YEAR(TODAY()),MONTH(A:A)=MONTH(TODAY())),DAY(A:A)>=DAY(TODAY())),1)

    The result is 1 even though the dates in cells A2 (05/10/23) and A10 (05/15/23) both meet all 3 conditions and should therefore return a result of 2.

    Please help to understand where I have gone wrong. Thanks!

      1. Hi, I thought that A10 would qualify as its day (the 15th) is greater than or equal to today's day.

        PS - As a further test, I changed all of the dates to be non-qualifying (none of the years = 2023, none of the months = 5 and none of the days are >= today's day), but the result remains 1. I also deleted all of the dates, but the result continues to be 1. I must not understand correctly how the IF/Count statement works. Thanks.

          1. Thanks so much

  35. This is more of a COUNT issue than a COUNTIF but not sure if COUNTIF is what I need. I have the following formula
    =COUNT(IF(MONTH('Iveco-Home'!$M:$M)=12,1)), which looks for the month December. However I am trying to move it on a month to January but blank cells are reporting as January too, how do I get my formula to avoid the blanks? Thank you in advance

      1. Something isn't working and I can't see what the issue is. The data below is for column M:M but the formula is returning a value of 0 when I am expecting 1.

        Current del date
        Oct-23
        Oct-23
        Oct-23
        Oct-23
        Jan-24

        Any help greatly appreciated.

          1. Sorry to revisit this again but I am still struggling to get the formula to work. I created a new document just with the following data

            Oct-23
            Oct-23
            Oct-23
            Oct-23
            Jan-24

            and I could get the formula to work, however if I had more Jan dates I am still getting a result of 1?

            Again any help greatly appreciated.

            1. Hi! Try another version of the formula to count all the January dates and ignore the blank cells.

              =SUM((MONTH(M1:M100)=1)*(M1:M100>0))

  36. Hi,

    I want to pick "out" & "in" from a specific data rows, Which formula will work in excel without using text to column. As I have thousands of rows.

    Admitted 'Harold Nelson, John' (Card: 817252)   at 'METRO-PRODUCTION WALDO RECEPTION' (OUT) entering area METRO AREA INTERNAL.
    Admitted 'Harold Nelson, John' (Card: 817252)   at 'METRO-PRODUCTION WALDO RECEPTION' (IN) entering area METRO AREA PRODUCTION WALDO.
    Admitted 'Rojas Salas, Jorgue' (Card: 817218)   at 'METRO-PRODUCTION WALDO RECEPTION' (IN) entering area METRO AREA PRODUCTION WALDO.
    Admitted 'ESPINOSA, CARLOS ANDRES' (Card: 622867)   at 'METRO-PRODUCTION WALDO RECEPTION' (IN) entering area METRO AREA PRODUCTION WALDO.
    Admitted 'Harold Nelson, John' (Card: 817252)   at 'METRO-PRODUCTION WALDO RECEPTION' (IN) entering area METRO AREA PRODUCTION WALDO.

    1. Hi! Your task is not completely clear to me. To understand what you want to do, give an example of the desired result.

  37. Hi
    I am searching to find/count the intermediate holidays between two holidays or the current day depending on the date & day. In the following table Dates 3 and 10 are weekend Fridays. I want to count employee's Present (P), Absent (A) or Leave (SL/CL) depending on attendance, i.e. if an employee is present on the day before and after the weekend then the day off is counted as present. and if he is absent or on leave on the days before and after the weekend, his weekend will be counted as absent or holiday.

    Name Designation 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 P SL CL EL SPL LWP A - WHD

    Mr. A Abc P P P P P P P P P P P P P P 14 - - - - - - - 2
    Mr. B Def P P P P A P P p P P P P P P 13 - - - - - - 1 2
    Mr. C Ghi P P P P LWP P P A A SPL P P P P 10 - - - 1 1 2 2
    Mr. D Jkl P P P P P P P CL CL P SPL P P P 11 - 2 - 1 - 1 - 2

    Thank you.

  38. I'm trying to count cells with TRUE or FALSE in them.
    The TRUE or FALSEs were gotten out of an IF function.
    But anytime I use COUNTIF([cell range]),"TRUE"), I am getting 0.
    Why is it so, and what is the right thing to do?
    Thank you.

    1. Hello!
      The IF function returns a boolean TRUE or FALSE value, not a text value. Do not use quotes in your formula.
      COUNTIF([cell range]),TRUE)

      1. unfortunately, I tried the formula without the quotes, but I still get 0 as output. COUNTIF([cell range]),TRUE)

        1. the problem is the closed bracket after the [cell range]
          From your post COUNTIF([cell range]),TRUE)
          need to change to COUNTIF([cell range],TRUE)
          COUNTIF([cell range],TRUE)
          so it's looking for blanks.

        2. Hi!
          Read carefully in the article above how to use the COUNTIF formula. For example,

          =COUNTIF(B2:B10,TRUE)

  39. I'm trying to figure out a countif formula for Days Open based off today.

    90 Days Open

    Working off the below list.

    4/10/2022 4:00 1
    4/10/2022 4:01 11
    4/17/2022 4:01 4
    4/21/2022 12:35 2
    4/21/2022 12:39 1
    4/24/2022 4:00 5
    4/24/2022 4:00 1
    5/29/2022 4:00 10

    Any idea how to do this?

    1. Hi!
      I kindly ask you to have a closer look at the following paragraph of the article above: COUNTIF formulas for dates

  40. Hi,
    What formula can i use if i want to count a specific timeframe?
    Ex.
    Column A
    1:15 AM
    1:30 AM
    2:15 AM
    2:30 AM

    in Column B i want an output that will indicate how many are in range of 1AM-1:59AM
    in column C i want an output that will indicate how many are in range of 2AM-2:59AM

    Thanks

  41. Hello!

    I am in a pickle and could use some help.
    With what kind of formula can I count years?
    I have a list:
    1 year; 0 months
    2 years; 2 months
    1 year; 0 months
    0 years; 4 months
    3 years; 3 months
    1 year; 0 months
    And so on.
    I would like to count them like this
    <1 year; 0 months
    1 year; 0 months <2 years 0 months
    And so on

  42. I Want to count if the value is =>6 count 6,if Value is 7-15 count 10, if value is 16-30 count 15, if value is 30-90 count 20 and if value is 90-200 count 50, and if value is 200 > count 100

    DOH Count
    15 10
    23 15
    0 6
    107 50
    50 20
    239 100
    5 6

    would you please help to solve this issue?

  43. How to count only Dates in a column withh dates and names

  44. Hi,

    I've been looking through the blogs about COUNTIFS, VLOOKUP, IF and DATES, but it still puzzles me. I'm in need of the formula to seek for the latest date (Column A) on a specific items (Column B) for its amount (Column C) to show up in a new column. For instance, I would required the latest amount of Item A on 5/3/22, which is supposed to come back at 6. Anything after 5/3/22 should not reflect in the new column.

    Column A
    2/3/22
    4/3/22
    5/3/22
    5/3/22
    6/3/22
    6/3/22
    6/3/22

    Column B
    Item A
    Item B
    Item A
    Item A
    Item A
    Item A
    Item B

    Column C
    4
    4
    5
    6
    5
    3
    5

      1. Hi,

        I've gone through some checking and found out that the XMATCH function only applicable to Excel 365 above. I'm currently using an Excel 2019 which did not support the new function. Would hope that there is a formula to reflect the previous formula given in Excel 2019.

        Thank you

        1. Hello!
          To find the last match in a range of data, select the row with the highest row number using the MAX function. Use this array formula:

          =INDEX($C$1:$C$7, MAX(($A$1:$A$7=F1)*($B$1:$B$7=G1)*ROW($A$1:$A$7)))

          Press Ctrl + Shift + Enter so that array function works.

          1. Hi Alex,

            Thank you for the wonderful formula. The formula works perfectly fine, but only if the first row starts with Row 1. If I were to start it on other rows, the formula will not come back to the designated results be it array function or not. For instance, if I started it out with Row 2, an array function will come back with 5 and without the array function will come back with 4. What was the issue that caused for the differences?

            1. Hello!
              Subtract from the row number the number of rows before the start row. If it starts on line 2, then subtract 1.

              =INDEX($C$2:$C$8,MAX(($A$2:$A$8=F1)*($B$2:$B$8=G1)*(ROW($A$2:$A$8)-1)))

              1. Hi Alex,

                Fantastic! Thank you once again for your assistance. Very much appreciated.

      2. Hi,

        I've tried on the formula given but it does not come back with any number. Instead, it only shows "#NAME?" in the column.

  45. How to use COUNTIF to count the number of times a cell appears in a range.
    For Example: in cells C10:C33 I have equations, and I want to know within that cell range how many times F2 appears in the equation.
    This is to track an expenditure “automatically”

    If COUNTIF cannot be used for this then what is your suggestion?

    1. Hello!
      If I understand the problem correctly, get the text of the formula using the FORMULATEXT function and find the text "F2" using the SEARCH function.

      =COUNT(SEARCH("F2",IFERROR(FORMULATEXT(C2:C20),"")))

      Hope this is what you need.

      1. Thanks for your reply, interestingly it doesn’t want to acknowledge that we’re doing a range. I tested it on single cells and it works, giving me a value of 1 when it contains “F2” and 0 when it doesn’t.

        Once I introduce the range of C10:C33 it just gives me a value of 1 instead of counting the entire range.

        It’s a problem with the formulatext only accepting the first cell in the range.

  46. I am going crazy and am new to spreadsheets.
    I am using
    =COUNTIF($D$53:$D$82,"Yes")/COUNTIF($D$53:$D$82,"*")
    This range is 31 cells and represents days in the month. The result is reading as [0/32 days] - which is very close to what I need except the range should read /31 days. I'm boggled.

  47. I tried using the countifs formula to know the number of items within a specfic date range..

    Apple jan-april total 100

    It worked well.

    But when I tried to use the same formula in finding the total number of apples per month. It doesnt seem right.

    Because when i tried to add the number of apples from jan - apr, im not getting the total 100 from the 1st formula.

    Can someone pls help?

    1. Hi!
      I don't know what formula you are using and can't see your data. Please re-check the article above since it covers your task. Or describe the problem in more detail.

  48. Is there a way to count only the cells in a row that have visible data? Meaning it doesn't count cells with formulas in it when the formula is telling the cell not to show anything, like by using "" ?

    Thanks for your consideration.

    1. Hello!
      If I understood correctly, check the value "" in the cell. For example,

      =COUNTIFS(A1:A5,"<>",A1:A5,"="&"")

  49. Thanks for your very useful blog.
    I just wish to know how you display the results of countifs. for example if I wish to find numbers >= 5 in a long list of numbers, countif return their count but I need to display also the numbers or list of numbers which is/are greater or equal to 5.

    Best Wishes
    Ali

      1. But filter function didn't work in excel 2016 any other suggestions?

      2. Thanks a lot.

  50. Hi. How do I get a count for years of service when my cells have decimals (must keep the decimals)? Example: If I have services ranging between 0-55 years and I want to calculate how many have worked for 5 years, but I have some at 5.1, others at 5.4, 5.7, etc. How do I calculate how many of employees are celebrating their 5 years (including 5 to up to 5.9, but not 6) this fiscal year?

    I tried these: =COUNTIF(J2:J113,">=5*=5=5"); =COUNTIF(J2:J113,"*~5*"); =COUNTIF(J2:J113,"~5*"); =COUNTIF(J2:J113,"~5.*"); =COUNTIF(J2:J113,"*5.*"); =COUNTIF(J2:J113,"5.?"); =COUNTIF(J2:J113,"5."); =COUNTIF(J2:J113,"5"); =COUNTIF(J2:J113,"=5""=5""=5,<=6"), and countless others, but they don't work.

    What am I missing? I just want to know how many people to celebrate in increments of 5s at our facility, but can adjust the formula once I have the 5 years ones.

    Thank you in advance for your help.

    Blessings.

    1. Hello!
      To count the number of values in an interval, use the COUNTIFS function with two conditions:

      =COUNTIFS(J2:J113,">="&5,J2:J113,"<"&10)

      Or use two COUNTIF functions:

      =COUNTIF(J2:J113,">="&5)-COUNTIF(J2:J113,">"&10)

    2. Oh my goodness! Could it be =COUNTIF(J2:J113,">=4.5")-COUNTIF(J2:J113,">6") ?!
      As soon as I posted my frustrated question, I thought of this one. Is this correct?

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