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 4. Total comments: 579

  1. Hey would anyone know how to use COUNTIF to compare two columns of data - dates, only counting IF the first column date is proir to the second? 30,000 data points so I cannot do it manually

    1. Nick:
      I think this is what you're looking for:
      =COUNTIF(E6,">"&D6)
      Where the dates are in columns E and D beginning in row 6 enter this in column F.

  2. IFEOLUWA AKINNLO W/D ENGINEER
    RAWLINGS U UWUIM BRC OPERATOR
    JIMOH ISHMAEL AD W/D OPERATOR
    AKANDU OKECHUKWU ELE Electrician
    HENRY EYO DAVIS ELE Electrician
    JOHN ETIM W/D OPERATOR
    FRANKLIN IGHOROD ELE HOD
    LASISI OLADIPUPO ELE D.G OP
    ALEX ADEWOLE W/D OPERATOR
    JIMOH RASAQ W/D OPERATOR
    ADELEKE KAZEEM BRC OPERATOR

    I WANT TO COUNT HOW MANY OPERATOR PRESENT DATE WISE. USING COUNTIF FUNCTION.

    1. Dillip:
      If the data is in the range A2:C27 the formula in D2 looks like:
      =COUNTIF(A2:C27,"Operator")

  3. For some reason the COUNTIF formula with dates isn't working on my spreadsheet. Say I have a list of dates in M/DD/YYYY format across multiple rows and columns. I want to use a formula to count how many of those dates fall into a particular week.

    A B C D
    1 4/26/2018 5/08/2018 5/15/2018 5/17/2018
    2 4/26/2018
    3 4/24/2018
    4 4/26/2018 5/15/2018
    5 4/24/2018
    6 4/30/2018 5/16/2018
    7 4/23/2018 4/30/2018 5/17/2018 5/14/2018

    =COUNTIFS(A1:D7,">=&4/20/2018",A1:D7,"<&4/30/2018")
    It should return 7, right? So why is it returning 13? I use the "&" because otherwise the value returns a 0. I'd appreciate the help.

    1. Jon:
      Does your Excel version support COUNTIFS? I use COUNTIFS for this and got the correct number which is 6.
      =COUNTIFS(O23:R29,">=4/20/18",O23:R29,"<4/30/18")
      When I changed the second criteria to be "<=4/30/18" the result is 8.

  4. So I was wondering if and how you can set the criteria of COUNTIF so that one row equals another? Meaning I want to count B2:B100 if it equals A2:A100, in other words counting how many times column B equals column A in the same row.

    1. In column C, I would add an if statement.

      if(A2=B2,1,0) then AutoSum (or countif) column C.

      1. Just found a better (quicker) way!!!

        =SUMPRODUCT(0+(A2:A15=B2:B15))

        Thanks, Barry! https://superuser.com/a/846065

  5. how do I get countif to count the value in a cell, i.e. if the cell references another cell or is a formula, "=H40" countif will not count the value in the cell, but only what is written in the cell. How do I work around this???

    1. will yes counties does work on some formulas but not the one I am interested in

      =COUNTIFS($H$50:$L$50,"=10")

      will not count the output of

      =TEXTJOIN("",TRUE,IFERROR(MID(H49,ROW(INDIRECT("1:100")),1)+0,""))

      there are values in the cells that countif just does not see and all I get is zero.

      1. found the problem numbers were being stored as text

  6. please help... my professor asked as to count the name of anne so we make the formula =countif(A1:A12,"*anne*") but we need to make the string color green when the excel counts the name anne... what formula should we use? please i need answer in this confusing activity

  7. i want to fine a value for a cell based on database

    number value need ?
    5 1 5 what is value
    8 2
    2 3

  8. Helllo - Please can you assist.
    I am currently using the following formula and when there is nothing to display it shows a 0 how do I change the formula to show blank instead of 0

    =COUNTIF(Table1113[@[CCN1]:[Plant & Appliances in Non Domestic Premises]],">"&TODAY())

    many thanks

    1. I would use the following to hide 0 results:

      =if(COUNTIF(Table1113[@[CCN1]:[Plant & Appliances in Non Domestic Premises]],">"&TODAY())=0,"",COUNTIF(Table1113[@[CCN1]:[Plant & Appliances in Non Domestic Premises]],">"&TODAY()))

      I am sure there are better answers than this, but it works for me.

  9. I have 2 columns of data. Column A contains a date. Column B contains a temperature. Temperature measurements are made several times each day, so several rows will have the same date. I'm trying to determine how many unique days a specific temperature is reached. So, if a temperature is reached several times during the same day, it should only count as "1" occurrence.

    1. Hi Steve! I have the exact same problem! Did you ever figure out how to do it?

  10. PLEASE PLEASE HELP!

    If I want to count or sum a COLUMN, I can do =sum(F:F) for column F

    Let's say cell A1 has F in it,

    How can I reference A1 to do sum (so I can change that cell to do different columns like G, H, I, J).

    I tried like =sum(&A1&:&A1&) but that doesn't help - I was hoping that would calculate as = sum(F:F)

    Thanks!!!!

  11. I would like to count non-empty cells, but using the count if formula, it also counts the empty ones because they have a formula that return a value of nothing/blank/empty. How to make excel not to count these formulated empty cells?

  12. Hello, I want to count qualifications. Column A has the number of days a qualification is good for, Column B has the date the qualification was completed. I want to take today's date - date qualification completed and count if it is less than the day value in Column B. Can you help?

    1. I need a formula with 3 conditions
      count if column AS = "sold", column B = "x" and column J = "y"

  13. Hi,

    I have a problem to find the proper formula for a sheet that trying to create.

    In column A, I want the user to type the code of a customer.
    That i have sorted with a data validation where only one entry is allowed.
    However, only one entry per day. So the next day, the code should again be used.

    CODE Date TIME
    100 18/11/2017 14:55:36
    100 18/11/2017 14:55:36

    As you can see here, the code 100 can be used twice but that is what i would like to avoid.
    The code 100 can only be used 1 time per day.
    The sheet is for marketing purpose where the transportation expenses of a visiting customer is paid back. But only one time per day of course.

    Is there a way, if possible with a data validation where i can enter a code to have a validation based upon a one time per day entry ?

    Or one time 100 per day entry but the following day 100 is again usable but also then only 1 time.

    I have been struggling with this issue now for 2 days and i simply can not find it.

    Thanks ever so much for the support.

    Eric

    1. Hello,

      Please create a custom Data Validation rule for column A using this formula:

      =COUNTIFS(A:A,A1,B:B,B1)<=1

      Hope it will help you.

  14. Olá!

    Gostaria fazer a contagem de células preenchidas (AI:AW), porém apenas das linhas indicadas por um critério (coluna I). É possível?

    Muito obrigada!

    1. Hello, Maria Pessoa,

      Please try to use one of the following formulas:

      =SUMPRODUCT(((I:I)="critério")*NOT(ISBLANK(AI:AW)))

      Or

      =COUNTIFS(I:I,"critério",AI:AI,"<>"&"")+COUNTIFS(I:I,"critério",AJ:AJ,"<>"&"")+COUNTIFS(I:I,"critério",AK:AK,"<>"&"")+COUNTIFS(I:I,"critério",AL:AL,"<>"&"")+COUNTIFS(I:I,"critério",AM:AM,"<>"&"")+COUNTIFS(I:I,"critério",AN:AN,"<>"&"")+COUNTIFS(I:I,"critério",AO:AO,"<>"&"")+COUNTIFS(I:I,"critério",AP:AP,"<>"&"")+COUNTIFS(I:I,"critério",AQ:AQ,"<>"&"")+COUNTIFS(I:I,"critério",AR:AR,"<>"&"")+COUNTIFS(I:I,"critério",AS:AS,"<>"&"")+COUNTIFS(I:I,"critério",AT:AT,"<>"&"")+COUNTIFS(I:I,"critério",AU:AU,"<>"&"")+COUNTIFS(I:I,"critério",AV:AV,"<>"&"")+COUNTIFS(I:I,"critério",AW:AW,"<>"&"")

      Hope it will help you.

  15. Attempting to use Countif to determine month, however data range has date stamps

    data example:

    Row A1 "01/11/2017 08:25:44"
    Row A2 "31/10/2017 12:42:16"

    attempted countif:
    =COUNTIF(A1:A2,"nov") produces no result???
    please advise

    1. Hello, Rich,

      Please try the following formula:

      =SUMPRODUCT((MONTH(A1:A2)=11)*1)

      Hope it will help you.

  16. I am trying to count the numbers that not equal in one column. for example

    1
    2
    3
    3
    3
    5
    5

    I am using count if but it's not working.

    1. Hello, Sam,

      if I understand your task correctly, this formula may help:
      =SUM($A$1:$A$7*($A$1:$A$7<>OFFSET($A$1:$A$7,1,0))+IF($A$7=OFFSET($A$7,1,0),$A$7,0))

      If it won't, and you need to exclude any number that appears more than once, feel free to try our Duplicate Remover add-in first, and then SUM function to sum the results.

      Otherwise, you will need VBA code or a macro, but I'm afraid we won't be able to help you with those.

  17. I am trying to count certain text in a range of cells from another tab. I need to count "status" and "status date" as separate formulas for counts. I tried this but didn't work: =COUNTIF('690-JHC3 Ven 600-699'!F4:F10001,"*STATUS*")and the other =COUNTIF('690-JHC3 Ven 600-699'!F4:F10001,"*STATUS DATE*")

    Appreciate the help - thanks

    1. Hi Kim,
      Try this
      =COUNTIF('690-JHC3 Ven 600-699'!F4:F10001,"*"&"STATUS"&"*")

      This should ensure that STATUS is counted no matter what is before or after the word.

  18. HI THERE ,

    I WANT TO CALCULATE NO OF UNIQUE ALPHANUMERIC CHARACTER(EXMP.I-INKOL17000522 OR IN17000523) MONTH WISE REMOVING DUPLICATE .NEED TO USE ONE DROP DOWN LIST OF MOTHS TO GET MONTH WISE TOTAL NO OF OCCURRENCE.
    PLS HELP

    1. Hi, Sammie,

      I'm sorry, it's difficult to help you with a formula since we don't know how your data is stored.
      However, If I understand your task correctly, first of all you need to sum unique values. You can do that by following the instructions from this article.
      And then, using these results, you create a drop-down list.

      Hope you'll find this information helpful!

  19. Please suggest formula -

    If in Column A I have names (Names are repeating) & In Column B Status against name like Yes & No.

    So If I want to count how many Yes are there against any particular name than how to do it. Pls help

    1. Hello,
      assuming, that you want to count "Yes" against "Peter" use the following formula:
      =COUNTIFS(A:A,"Peter",B:B,"Yes")

      Just replace the name in the formula with the one from your data.
      Hop it helps!

  20. I have 40 students, I wanna use COUNTIF to know how many students got from 7 to 7.9. I wrote it like this. +countif(a2:a41;">=7"&"<7.9") is it correct?
    Thanks in advance!

    2. This should give you the answer

      =COUNTIF(a2:a41,">7")-COUNTIF(a2:a41,">7.9")

    3. No it should be

      =COUNTIF(A2:A14,">7")-COUNTIF(A2:A14,">7.9")

  21. Hi!

    I have a spreadsheet with columns A and B. In column A is new results, in column B is old results. I need to count the number of occurances where the value in column A is greater than the corresponding value in the same row of column B.

    I have tried using
    =COUNTIF(A2:A100,">"&B2:B100)but it always returns 0 even though there are many instances of an A value being different than the corresponding B value

    I also tried =COUNTIF(A2:A100,">"&B2)But that returns the number of A values greater than B2 specifically instead of the corresponding B Value of the same row.

    Please Help!!!

    1. Hi, Kate,

      SUM function will solve your problem, try this:
      =SUM(--(A1:A4>B1:B4))
      Finish entering the formula by pressing CTRL + SHIFT + ENTER instead of just Enter.
      Another option is:
      =SUMPRODUCT(--(A1:A4>B1:B4))

  22. Hi there...I have data that lists names of people and how many times they register per month. So Column A is a list of names and there could be multiple occurrences. If you are only supposed to register 2 times a month and i would like a way to identify by name those that registered more than 2 times a month and the frequency of times they registered. How do i do this?

    1. Honestly, a pivot table would probably be the easiest way to do this.

  23. I need to count the number of names in a single cell (example B14).

    I don't want to do so by specific names since the worksheet needs to be applied to a large number of people. I have tried the formula =countif(B14,"*").

    However when I enter two names in cell B14 Smith Jones it only counts it has 1 instead of two.

    Does anyone know how to solve the issue.

    Thx

    1. This is for Dave...
      =IF(LEN(TRIM(B33))=0,0,LEN(TRIM(B33))-LEN(SUBSTITUTE(B33," ",""))+1)...
      hope it helps. if i understood your question correctly.

      change the cell reference in the above function to your liking.

      Thanks
      P.S. Great Post on COUNTIF...

      1. Thanks for the help

  24. What is wrong with this formula? It produces a wrong value if zero.
    =COUNTIF(B2:B86,"Not Done")

    1. sounds like cells have 0 values
      use this =COUNTIF($B$2:$B$86,"0")

      or use two formulas in different cells:
      1. =IF(COUNTIF($B$2:$B$86,"0"), "not done","done")
      2. use this =COUNTIF($B$2:$B$86,"0")

      IF THE Cells are blank then use this
      =COUNTIF($B$2:$B$86,"")

      ciao

  25. A B C D E F G H I J
    B 1 0 1 1 1 0 1 1 1
    C 0 1 0 0 0 1 0 0 0
    D 0 0 1 1 0 0 1 0 1
    E 1 1 1 0 1 1 0 1 0
    F 0 1 0 1 0 0 1 0 1
    G 0 0 1 0 0 1 0 0 0
    H 1 0 0 1 1 0 1 1 1
    I 0 1 1 0 0 0 0 0 1
    J 1 0 1 1 1 1 1 1 0

    find duplicate column..
    highlight it

    Help me with this...

    1. can anyone help me with this ??

      i can find the duplicates column in alphabetic mode but i can't in numeric mode

      please help me out with this

  26. What about count if greater than 5 and less than 8 for example? or between something?

  27. I WANT TO COUNT WITH IF BUT AT NEGATIVE
    EXMPL
    SUMIF -> IF ISNT "THIS"
    ?

    1. Hi Greg,

      You can use the "not equal to" operator (<>) e.g.:

      =COUNTIF(A1:A10, "<>"&5)

      =COUNTIF(A1:A10, "<>"&"text")

  28. I need help counting unique invoices touched by the collector per work day. I used If(I1=I2,0,1) but still getting some duplicate invoices for others. I need column I to give the count of unique invoices touched that day.
    Thanks,
    JM

  29. Hi Svetlana,

    Thank you for this very helpful article. :) I'm trying to use the countifs function, however it's not working for me. Here's the scenario.

    I have one question which shows a list of anawer.

    Ex: How likely will you buy this?
    Product 1, Produt 2, Product 3
    In each cell there's a drop down list of the answers such as 3- will buy it, 2-somehow will buy it ans 1-not going to buy it.

    I'm using =countifs(A2:A102,"3", A2:A102,"2", A2:A102, "1")

    but it's not even counting :(

    Hope you could help with this.

    Thanks a lot!

    1. Hi Kristiane,

      First off, remove double quotes surrounding numbers because they turn numbers into text strings.

      If you need to get the total count of answers 1, 2 and 3, then add up 3 different COUNTIF functions, because COUNTIFS works with the AND logic while you need OR:
      =countif(A2:A102,3) + countif(A2:A102, 2) + countif(A2:A102,1)

      If you want the individual count for each type of answers, then use 3 separate formulas like this: =countif(A2:A102,3)

  30. please help need to find a way to to count how many times a person was late,i.e. d2:d25 =paul,e2:e25 =are times started ie 10:00,10:15 ect.start time is 10:00. i use this,COUNTIF(e2:e25">10:00")for the total count,but i need to find a way
    to count for each employee thank you

    1. Hi Jum,

      You can use COUNTIFS to count with multiple criteria:

      =COUNTIFS(E2:E25, ">10:00", D2:D25, "paul")

  31. hi
    I wanted to enter these values 2724742560in a cell of a spreadsheet but after pressing the enter key the cell displayed #########. please explain why and how could I rectify the situation.

    1. Hi Michael,

      Most likely the cell is not wide enough to display the value, so just make it a bit wider. To quickly change the column width to fit the contents, select the column, and double-click the boundary to the right of the selected column heading.

  32. state the results of the function,=IF(COUNT IF(B2:B9,">65")>5,"YES","NO")

    1. Michael,

      If the range B2:B9 contains more than 5 cells with values greater than 65, return YES, otherwise NO.

      BTW, COUNTIF should be spelled with no space in between like this:
      =IF(COUNTIF(B2:B9,">65")>5,"YES","NO")

  33. I have a table in which first column i add date 1 July to 26 July 2016. In second column, I give rating to me (e.g. 0 to 10). I want to sum my rating only past 5 days but when last 5 day rating is less than 2, then add one more day. e.g.
    Date Rating
    15 July 5
    16 July 3
    17 July 0
    18 July 8
    19 July 2
    20 July 6
    21 July 7
    23 July 1
    24 July 5
    25 July 6
    26 July 3

    when I sum last 5 day (21 July to 26 July) is 22, but 23 July my rating is less than 2 than I add one more day in rating (20 July to 26 July) than my rating is 27, I remove 23 July in my life.

    Can you you help me in this.

    Than You.

    1. any one can help me in this.

  34. I want to count when a range of cells (m8:m17) are not equal to 0 but only when cell m6 isn't 0.
    Is this possible?

    1. Hi!

      The following formula counts how many cells in the range M8:M17 are not equal to 0 when M6 is not equal to zero, and returns an empty string (blank cell) otherwise:
      =IF(M6<>0, COUNTIF(M8:M17,"<>"&0), "")

      If you are looking for something different, please clarify.

  35. Please help me to come up with a formula used to show exact date in which data was entered in a cell.

  36. column A column B
    1200018401 9000035943
    1200018402 9000035944
    1200018392 blank
    1200018393 blank
    1200018396 blank
    1200018397 blank

    How can I calculate whenever column A has a number and column B has a blank. I need to know that total blank number

    1. Dear Jen,

      Use the formula
      =COUNTIFS(A1:A100,">"&0,B1:B100,"")

      Change the range of the cells as required.
      Do let me know if this is what you wanted to do.

      Regards

      Vijaykumar Shetye, Goa, India

  37. I'm trying to use the COUNTIF function to count cells in a column that are >0, but I don't want it to include hidden cells. How do I select an entire column as my range, but not include hidden cells in my count?

    1. Here is the formula that I tried using =COUNTIF(Compile!N:N,">0")

  38. hi,

    we have to required result how many R0715, R0716, R0718

    R07150101
    R07160110
    R07150122
    R07180129
    R07150132
    R07180137
    R07170139
    R07120147
    R07150163
    R07150168
    R07150172

    1. If your codes are in coloumn A...

      =COUNTIF(A1:A12,"R0715*")

  39. Hi All,

    Can I count a value that exists in columns rather than cells? For example, I want to know how many columns contain the number say 3, even if a single column has more than once cells that contains "3". I am interested in knowing the number of columns not how many times number "3" exists

    Thanks in advance!

    1. Below is the formula,

      =COUNTIF(A1:Z100,3)

      Change the range of the cells as required.

      Vijaykumar Shetye, Goa, India

    2. I would start by setting up a helper column containing an IF formula wrapped around a COUNTIF, for each column you are searching. For example, if columns A-F are the ones you are searching, then use G-L for these formulas. Each IF formula is to convert the results of a COUNTIF, into a 0 or 1, indicating whether that column contains that value.

      Then in column M, use a SUM function to add up the results from the Helper columns G-L.

  40. Hi
    Value in cell A3 is 10
    I want to check this value tn 4 Different Criteria
    1)Less than 20
    2)Greater than 20 but less than 50
    3)Greater than 50 but less than 80
    4)greater than 80
    Please help
    Regards
    Venkat

    1. Hi Venkat,

      Can you give more details on what you want to do? It looks like you have just 1 cell with a value but multiple mutually-exclusive criteria (10 cannot be less than 20 and also greater than 20). You may want multiple cells that use the COUNTIF function, or if you want to test the same cell for multiple criteria, use COUNTIFS.

      Hope that helps!

  41. Hi madam,
    iam struck with one error problem in excel.
    There is list of students name in columnA : A1:A10
    and also more students name in ColumnE : E1:E10
    Now i want to count, how many times each name is repeated in both columns A and E using countif function.
    Between A and E we have B, C,D columns contains different data columns.

    COUNTIF(range, criteria)
    i am not able to add both range under single criteria in countif function.

    Please help me with the solution.
    How to add two non adjacent column under single criteria.

    =COUNTIF( both range?, "ravi")

    A B C D E
    1 ravi 435 Teja
    2 sneha 250 sunil
    3 anil 136 ravi
    4 teja 786 sandy
    5 sunil 250 praveen
    6 reena 587 teena
    7 naveen 121 praveen
    8 sunil 456 anitha
    9 teena 454 reena
    10 sunil 895 anil

    1. Hello, Praveen,

      Here you are:
      =COUNTIF(A1:A10, "ravi") + COUNTIF(E1:E10, "ravi")

      1. can it possible to put both ranges A1:A10 and E1:E10 in single array like { A1:A10 E1:E10 } to reduce the formula length.

  42. Hi bro(s),

    I have a question about using COUNTIFS to count number of rows that have status different from "completed" with the example below:

    Status-------Value
    completed 1
    started 2
    canceled 3
    delayed 5

    Result expectation: 3

    How can I count with the exactly string excluded? Please help me answer, I need your help!

    Many thanks,
    Ngan Trinh

    1. Hello, Ngan,

      Please try the formula below:
      =COUNTIF(A2:A5,"<>"&"completed")

      here the Status column is in A1:A5

  43. I want to SUM up a row but the total has to be either a sum up or blank if there is an empty value (a blank cell).
    i.e.
    A B C D(A1+B1+C1)
    1 12 28 30 70
    2 50 20 17 87
    3 10 34 06 50
    4 "Blank"
    5 20 20

    I've tried
    =COUNTIF(G17,">0")+COUNTIF(H17,">0")+COUNTIF(I17,">0")

    also

    =SUM(IF(ISBLANK(G17),IF(ISBLANK(H17),IF(ISBLANK(I17),G17+H17+I17,""))))

    as well as

    =SUM(IF(G17:I17"",G17:I17,""))

    The answer of total still wrong.

    How should I set the formula at D?

    Thanks.

    1. Hello, Peggie,

      Please try this one:
      =IF(OR(ISBLANK(A1), ISBLANK(B1), ISBLANK(C1)), "Blank", SUM(A1:C1))

  44. I have a column of UPS, FedEx, and DHL tracking numbers. I am trying to sum up the total # of tracking numbers in that column. How can I do this when the tracking number has both text and number context in it?

    e.g.:
    36006962160000026558847083172014
    1Z9V14091300013079
    7614784914

    1. Have you tried using the following code?:
      =COUNTA(A1:A3)

  45. I have the following data:
    Case Rate
    A 0
    A 2.58
    B 0
    B 0
    C 13.45
    C 0
    C 0
    D 0
    D 0
    D 0

    I need to determine how many of the case groups have a total of Zero. In the above example, it should read 2 case (Case B & D) arethe only one that has a zero total

    1. Note - the case letters are only for this example, usually it will be different names

      1. I have tried the following formula but it doesn't produce the results that I am looking for: =COUNTIFS(A2:A16,A2:A16,B2:B16,0)

  46. Hi, I want to count all cells that have numbers only. I have some cells that have NA and I want to exclude those from the calculation.
    thanks,
    Di

  47. Hi Svetlana, I'm trying to identify duplicates in a column based on values in adjacent cells. For example:
    A B C D E
    375 500 Cheddar (D)
    Soft Brie
    125 Hard Cheddar
    125 375 Cheddar (D)
    375 Soft Brie

    A & B are number format; C, D and E are Text format.
    For a duplicate to be correct A & B must have numbers present, C & D must be blank - I have indicated the duplicates here with a (D) but not needed in the formula. If I had hair I would have lost it today trying to figure this out - please help.

    1. Hi Roy,

      If my understanding of the task is correct, the following formula should work a treat:

      =IF(AND(ISNUMBER(A1), ISNUMBER(B1), C1="", D1=""), IF(COUNTIF(E:E, E1)>1, "dupe", ""), "")

      1. REF: 213
        Hi Svetlana many thanks for the quick reply. I was just venturing in to the ISNUMBER function when your solution arrived. Yes it works thank you, however, it doesn't wait for a comparison before stating "dupe" - my fault for not being very clear. Unless I'm mistaken ISNUMBER only caters for a single cell and not a range - is there a way around this please?

        1. Hi Roy,

          Yes, ISNUMBER accepts only an individual cell in the argument. Sorry, I do not quite understand what you mean when saying "it doesn't wait for a comparison before stating "dupe". Please specify what cells should be compared.

          The formula I suggested works with the following logic:

          If A1 & B1 have any numbers in them, and C1 & D1 are blank, check column E for duplicate values (i.e. check if the value in E1 occurs in any other cell in column E). If one or more duplicates are found, the formula returns "dupe", an empty string otherwise.

          1. Hi Svetlana
            Essentially, when I re-read what I have asked of you, you have responded by producing exactly what I requested - my apologies for the lack of clarity. To keep it its simplest terms, I have a range of cells A1:E20 where a duplicate condition is met when a row has A1 & B1 with numbers in them and both C1 & D1 are blank, E1 would then be checked for duplicate values. However, on the first occasion that this occurs it cannot be considered a duplicate it is a unique value until a second occurrence appears etc. I have been trying COUNTA in place of ISNUMBER, still not quite there but I think it's the last bit that might be the key.
            Once again apologies for the confusion, I really appreciate what you and the team at Ablebits do in supporting us lesser mortals.

            1. Hi Roy,

              No need to apologize. From my own experience, it's very difficult to exactly explain/understand the task without seeing the same source data :)

              Please try the following formula. It identifies duplicates without 1st occurrences:

              =IF(AND(ISNUMBER(A1), ISNUMBER(B1), C1="", D1=""), IF(COUNTIF($E$1:$E1, $E1)>1, "dupe", ""), "")

              Is this what you are looking for?

              1. Yes Svetlana - works a treat - great job - have a good weekend!

  48. Hi Please im begging for your help on this please.

    i have a problem with countif. how will i count Arriola with more than 2 days? i dont want to count arriola i want to count only arriola with more than 2 days.. please

    A B MORE THAN 2 days
    Lionel 2 Lionel = 0 << LIKE THIS RESULT
    Lionel 2 Arriola = 1
    Arriola 3
    Arriola 1
    Lionel 2
    Arriola 2

    1. Hi!

      To count cells with 2 or more criteria, you need to use the COUNTIFS function.

      Supposing that "Arriola" is in column and the number of days in column B, you can use the following formula:
      =COUNTIFS(A:A, "Arriola", B:B, ">2")

  49. A B
    1 Grn:1 11-01-2016
    2 Grn:2 11-01-2016
    3 Grn:2 11-01-2016
    4 Grn:3 11-01-2016

    in this table i want to count in 11-Jan-16 how many GRN I received it should not count double like GRN:2 i received 2 time in same date so i want to count only one time.

    1. Hello Sunil,

      I recommend adding a column that will check if the values have duplicates. Then you can use the COUNTIFS function to calculate the result by two criteria.
      https://www.ablebits.com/office-addins-blog/excel-countifs-multiple-criteria/

      Please see a sample file with the functions that I described:
      https://support.ablebits.com/blog_samples/excel-countif-examples_210.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

  50. I am having one doubt regarding counting value like

    A B
    1 Grn:1 11-01-2016 in this table i want to count in 11-Jan-16 how
    2 Grn:2 11-01-2016 many GRN I received it should not count double
    3 Grn:2 11-01-2016 like GRN:2 i received 2 time in same date so i
    4 Grn:3 11-01-2016 want to count only one time.

    Please help

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