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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  31. Hello,
    This Formula works =COUNTIFS(COUNT, B26, CO, "FSC",STATUS, "OVERDUE") but I want to a count of how many OVERDUE are over 120 Days.
    B26 = the Category
    CO = the B Column
    STATUS = the F Column and OVERDUE is in that Column
    DAYS = Column G
    I want a count of the category specified that is in the CO specified that has a status of OVERDUE with days that are greater than or equal to 120.

    Any Ideas?

    1. Hello,
      Thank you for your reply! It is a number and I want to count how many with that name have a number greater than higher than 120 in column G. and I have read this multiple times and I cant get it to do what i want. I'm not that smart, which is why I asked all of you (The Smart Ones) the question. I tried =COUNTIFS(COUNT, B27, CO, "FSC",STATUS, "OVERDUE",">="&120) and it says "You've entered too few arguments for this function". I have a cell that counts if the name in B27 that has a company "FSC" that has a Status of "OVERDUE" and it works but I need to know how many of those with that name, company, and status are equal to or over the number (120) in a different column.

      Thank you

        1. Thank you for you response!
          I tried it with no luck, it says #VALUE! where the count should be. I tried this a couple of different ways with the same out come.

      2. Just to be clear I I have tried more that one string. I have tried multiple in different variations to include dates with =COUNTIFS(COUNT, B27, CO, "FSC",STATUS, "OVERDUE","<="&TODAY()-120) and everything in the article that made since to what I was trying to accomplish ( In my option, I very well could be wrong and answer is staring me right in the face ( I've staring at this spreadsheet for a couple weeks now)).

    2. Hi!
      Read the article above carefully, it contains the answer to your questions.
      I don't know how the days are written in column G. If it's a number, then the condition is ">="&120
      If it is a date, then the condition is "<="&(TODAY()-120)
      For more information about counting with multiple conditions using the COUNTIFS function, see this article.

  32. Hi, I am looking to compare first and last names from two different sheets. First sheet is our master registration with over 500 names on it. Second sheet (and subsequent sheets) is/are individual event(s) that someone is signed up for.

    Column A is last name
    Column B is first name
    Column C on "Master Registration" sheet is the first event
    Column D on "Master Registration" sheet is the second event....and so forth

    I would like to have Columns C, D, etc. return a value to indicate the different events that someone is signed up for, based upon second, third, fourth sheets.

    The issue I am having: first and last names are separated into separate columns...

    Any help would be greatly appreciated. Thank you very much.

    1. Hello!
      If the second sheet (and subsequent sheets) also has columns with first name and last name, then try using the guide: Excel INDEX MATCH with multiple criteria.
      I hope it’ll be helpful. If something is still unclear, please feel free to ask.

  33. how this if(countif(A$1:A1,A1)=1 formula works

    Where (A$1:A1) & (A$1:A1,A1) is used

  34. I am trying to set up a vacation tracker where every V for vacation is counted as 1 and every .5V is counted as a half day. I tried to do a countif, but couldn't get it to work.

    1. Hi!
      Count with COUNTIF first with V, then with the second formula with 0.5V. Multiply the second result by 0.5 and sum both results.

  35. Hi Alexander!

    This may sound really silly, but here's the question: I have one row of numbers where I am adding all numbers in the row

    1 2 3 4 5 6 7 8 9 10 11 12 etc

    How do i get the count of cells until the sum in the row is less than or equal to 21?

    The answer of count must be of course 6

      1. Thank you Alexander!

        Just in time for handing the excel which is due today!

        Have a good one

  36. Hi there,
    Lets say I have this situation
    A B C D E
    A1 B1 C1 D1 E1
    A+ B+ C+ D+ E+
    A- B- C- D- E-

    I would like to get all values of letter A
    please help.

  37. Hello! Sorry, why in the formula for calculating COUNTIF within a range
    =COUNTIF(B2:B9,">5")-COUNTIF(B2:B9,">=15") there's a greater-than sign before 15? Shouldn't it be a less-than sign (<)? Thanks

    1. Hello!
      Unfortunately, you are mistaken. The formula counts values greater than 5 and then subtracts the number of values greater than 15. As a result, we get the number of values greater than 5 but less than 15.

  38. Will countif function counts cells with formulas in it?

    1. Hi!
      Of course. The COUNTIF function counts values that are written manually or obtained using formulas.

  39. Hi! I have been working on a formula and I wanted to ask if there is a workaround it. Columns C-O has data validation in it (Yes/No). For Column P (Remarks) wanted to do a sanity check so I have this formula =IF(COUNTIF(C1:O1,"No")>0,"Incomplete","Complete"). The default remark is complete - I wanted to know if the default remark can be blank instead of complete? Thank you.

  40. Hi,
    Please help with this task.
    I want to list the names who have not participated but registered for the period of last week (which starts on Monday) by comparing the list of participants.

    Column A: contains participants
    Column B: contains the date
    Column C: registered members

    Thank you

  41. I am trying to figure how to count an item

    ex. I have a set of dates throughout the year in 1 column and I have another column with a word in it; ex. open or closed.
    I am trying to see how I can count either open or closed from the date column?

  42. what is the difference between =COUNTIF('sheetName'!B2:B15, "=PASS") and =COUNTIF(sheetName!B2:B15, "=PASS")?

    thank you
    branko

    1. Hi Branko,

      If the sheet name includes spaces or non-alphabetical characters, it must be enclosed in single quotation marks. If the sheet name includes only letters, numbers and underscores, single quotes are not needed. For example, to refer to "Sheet1", you'd use the reference Sheet1!B2:B15. To refer to "Sheet 1", the reference is 'Sheet 1'!B2:B15.

      For more information, please see How to refer to another sheet or workbook in Excel.

  43. I have a large excel file with a lot of data.

    I know how to delete the duplicate values but keep one value.

    I need to learn another technique.

    For example, a particular column repeats some data twice, some data thrice, and some data for four or five times.
    I want to delete the first entry only. I don't want to delete the second, third or fourth entry. That means only the first entry or first data will be deleted, but all the second, third, fourth or the next entry will exist.

    Will you please tell me how I can do this?

    1. Hello!
      To determine the 1st occurrence of the duplicate, write down the formula in the additional column:

      =IF(AND(COUNTIF($A$2:$A$9, A3)>1,COUNTIF($A$2:$A3, A3)=1), "1st occurrence", "")

      In this case, your list begins at cell A3. Copy the formula down the column as needed.

  44. Hi, Can u help me
    ive got several problems with my excel worksheet>
    im trying to figure out how to count some data with random data like this :

    Dates 02/01/22 03/01/22 04/01/22 05/01/22 06/01/22 07/01/22 08/01/22
    Name
    Yoga CT PL CT CT PL DL DL

    and i want to get (CT) count data with range number that i provide from another cell, so ijust need to fill my range date cell and get total data number :
    A1 A2 A3
    Data Range 02/01/2022 04/01/22

    Thank u so much
    appreciate

  45. The tutorial Excel COUNTIF for blank and non-blank cells contains numerous errors.

    COUNTIF(range,"*")

    The tutorial states that this formula counts only cells containing any text values. It does not mention that it also will not count cells containing null text values that are the result of a formula.

    Consider a cell containing the formula =IF(D52908="kMDItemUserTags = (",MID(D52909,5,99),""). Note that the result of this can be, in fact usually is, null, i.e. zero in length. When the condition is false, the result of the COUNTIF function is wrong.

    This can be tested by having a cell with the formula ="" in the range of the COUNTIF function.

    COUNTIF(range,"")
    and
    COUNTIF(range,""&"")

    These also fail when a cell within the range contains a null value that is a result of a function.

    In the screenshot show in the tutorial, placing the formula ="" in cells C4 and C8 will illustrate this.

    COUNTIF(range,""&"*")

    Again, This also fails when a cell within the range contains a null value that is a result of a function.

    COUNTIF(range,"")

    Finally, a formula that works when the range contains formulas generating null values.

    But why use the COUNTIF function at all? Just using the COUNTBLANK(range) function will be simpler and more descriptive.

    But, all these (COUNTIF and COUNTBLANK) functions also fail if the range contains cell which contain one, or more, spaces, whether keyed in or the result of a formula. In other words, the length of the cell is greater than zero but the data consists only of spaces. Excel, apparently, does not consider spaces to be blank.

    1. Hello Mark,

      Thank you for your feedback. I will try to clarify the two main points.

      Zero-length strings. In terms of Excel, an empty string ("") is a text value - ISTEXT returns TRUE for it. The COUNTA function also regards zero-length strings as non-empty cells. Given the above, it's incorrect to say that COUNTIF formulas fail in this scenario as their results are consistent with the results of other Excel functions.

      Spaces. Like zero-length strings, spaces (and other non-printable characters) are text values. Therefore, cells containing only spaces are not considered empty and are not counted by COUNTBLANK or analogous COUNTIF formulas.

      Why do we use the COUNTIF function for counting blank/non-blanks? At the very least, to show that Excel provides different ways to accomplish the same task :) Of course, there is also a practical application - knowing how to express such criteria, you can build more sophisticated COUNTIFS formulas, say, to count rows that have blanks in one column and non-blanks in another.

  46. Hi,

    I am trying to create a dynamic formula for the below table to count no of invoices(unique).. When I use countif function the output which I am getting is counting all the row's. For Eg Doc # 20212463 this will be counted as 2 instead of 1

    I am looking for a formula like this - in the month of Sep on Thursday at 12 .00pm how many unique invoices were created. I really appreciate if you can help me with this.

    Doc # Wk day Time Month
    20212463 Tue 16:00 Aug-21
    20213081 Thu 19:00 Oct-21
    20212463 Tue 16:00 Aug-21
    20212704 Thu 12:00 Sep-21
    20212960 Wed12:00 Oct-21
    20212704 Thu 12:00 Sep-21
    20212705 Thu 12:00 Sep-21
    20212706 Thu 12:00 Sep-21
    20212707 Thu 12:00 Sep-21
    20212708 Thu 12:00 Sep-21
    20212488 Fri 17:00 Sep-21
    20212702 Wed 16:00 Sep-21
    20213074 Wed 14:00 Oct-21
    20212489 Fri 18:00 Sep-21
    20212292 Fri 22:00 Aug-21
    20212382 Sat 20:00 Aug-21
    20212788 Thu 18:00 Sep-21
    20213086 Fri 17:00 Oct-21
    20212292 Fri 22:00 Aug-21
    20212292 Fri 22:00 Aug-21
    20212292 Fri 22:00 Aug-21
    20212292 Fri 22:00 Aug-21
    20212292 Fri 22:00 Aug-21

  47. Sir
    can you please send this excel file on email

    COUNTIF in Excel - count if not blank, greater than, duplicate or unique

  48. Hi,

    Pls suggest me a formula if i want to count total particular time (hr:min) from given range of time.

    i.e. if somebody have performed shift duty from evening 20:00 o’clock to morning 8:00 o’clock and i want to count hours from 22:00hrs to 6:00hrs for that particular shift !

    So what formula will come there ?

    Thanks.

    1. Hi!
      If the end time is longer than the start time, then you need to add 1 day to their difference

      =IF(A1 > A2,A2-A1+1,A2-A1)

  49. Hi, please could you help. I am trying to count: I have a document of customer sales on my store,

    column A is style codes/skus (which will be duplicated). column b is units sold,

    I want to count the units in column B when they match a specific style code in column A so that I can see how many in total sold of a specific style.
    I have read this doc thoroughly and cannot seem to find a way to work it.
    many thanks!

  50. Hi!

    I need to count the number of countries from a list of employees, excluding those duplicated employees:

    For instance, I have three rows with the same employee (190175) from Spain, but I just want it to be counted once based on the empoyee ID.

    The formula I have is =COUNTIF($T$2:$T$30,'Spain'), but I dont know how to exclude those duplicated rows with the same ID. Could you help me?

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