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

  1. How can I use COUNTIF to check for duplicated entries in excel?
    For example, if I have this the file below, how can CountIF show where the same STAN duplicates.

    STAN Output
    1234
    2345
    1234
    5678
    890
    5678
    6930

    1. Edozie:
      Where the STAN data is in A2:A8 the formula is:
      =COUNTIF($A$2:$A$8,A2)>1 then copy it down the column.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  52. Hi Svetlana,

    I am trying to calculate the number of countries with a life expectancy between 50-55,55-60,60-65...etc.
    columnA: country name
    ColumnB: Average life exp.

    would i be able to use the =countif function to find he amount of countries with the given criteria?

      1. So how do you drag it down to reflect the count of the changing range without manipulating the individual cells manually.

        For eg. 50-65
        And next cell will be 70-75, etc

  53. Hello Svetlana

    I have a spreadsheet where i have to count the amount of unique rows in column C that had the value "UDL" in column N.
    Column C can have duplicates so somehow i need to find out how many unique ones there are

    1. Hello Peter,

      You need the following array formula (remember to press Ctrl + Shift + Enter to complete it):
      =SUM(IF( (--($N1:$N31="UDL"))*(--(COUNTIF($C$1:$C$31,$C1:$C31)=1)),1,0))

      With row 1 being the first, and row 31 being the last.

      Uniques imply that there is only one occurrence of the value in column C.

      If you need unique values plus the first occurrences of the duplicates, then you need to use a Helper column, e.g. column O, and enter the following formula into its first cell:
      =IF(COUNTIF($C$1:$C1,$C1)=1,"Unique","Duplicate")

      Paste the formula to all cells below.

      Then use the following formula to count:
      =COUNTIFS(N1:N31,"UDL",O1:O31,"Unique")

      You can also use the Helper column to count only uniques, then the initial formula should look the following way:
      =IF(COUNTIF($C$1:$C$31,$C1)=1,"Unique","Duplicate")

  54. Hello

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

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

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

    1. Hello Jess,

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

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

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

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

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

    1. Hello Lukas,

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

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

    1. Hi Paolo,

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

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

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

    Sincerely,
    Christoffer

    1. Hello Christoffer,

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

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

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

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

  58. Hi Svetlana,

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

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

    Thanks!

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

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

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

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

  60. Hi

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

    Thanks

  61. Hi Svetlana,

    Quick question on how to use the countif/countblank.

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

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

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

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

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

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

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

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

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

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

    1. Hi Matthew,

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

  64. Hello,

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

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

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

    Carol.

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

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

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

  66. Hi there -

    I am looking for help.

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

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

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

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

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

    1. Hi Silvester,

      You can add up 2 COUNTIF functions, like this:

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

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

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

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

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

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

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

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

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

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

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

    1. Hi Grace,

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

  72. Hello,

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

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

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

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

    1. Hi Douglas,

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

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

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

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

  73. Hi Svetlana,

    I am using excel 2003 :(

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

    I have tried this:

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

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

    Many thanks!

    1. Hi Gary,

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

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

      1. Hi Svetlana,

        Perfect! Thank you very much for that!

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

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

    1. Hello Nikki,

      Here you go:

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

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

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

  75. Hi Svetlana,

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

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

  76. Hi,

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

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

    Thanks
    Shwetha

    1. Hi Shwetha,

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

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

      And use analogous formulas for other calculations.

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

    1. Hi Kendra,

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

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

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

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

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

    Thank you for any help you can offer.

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

    2. Hi Debbie,

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

  80. Hi Svetlana,

    Can you help please?

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

    Thanks much.

    1. Hi!

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

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

  81. Hi,

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

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

    Thanks!

    1. Hi Lidiya,

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

  82. Hi,

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

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

    Thanks a lot in advance.

    1. Hi Sat,

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

  83. Hi Svetlana,

    Thank you for this very helpful topic!

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

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

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

    Thank you very much,

    Elena

    1. Hi Elena,

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

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

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

    how do i take all the count

    1. Hi Shoaib,

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

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

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

  86. Hello i am having a Count if issue

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

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

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

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

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

    1. Im sorry

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

  87. hi Svetlana Cheusheva..

    I think you are a MVP.

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

    I really appreciate your efforts.

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

    God bless you..

    1. Hello Bharat,

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

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

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

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

    1. Hi Donna,

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

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

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

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

  92. I want to know how to use the COUNTIF Formula if we want to count number of customers whose second letter of their names is as "A"

    1. Hi Hoda,

      Try =COUNTIF(A2:A100,"?A*") where A2:A100 is the names column.

  93. =COUNTIF(tblJanuary[@[1]:[31]],"V")+COUNTIF(tblJanuary[@[1]:[31]],"A")+COUNTIF(tblJanuary[@[1]:[31]],"S")+COUNTIF(tblJanuary[@[1]:[31]],"H")

    I want to count the number of certain values (V, A, S, and/or H) in row, but I do not want it to count any other values I might enter. I tried the above formula but it still counted if I enter something other than V, A, S, H. Any ideas?

    1. Nevermind - Actually, it is working! Happy Holidays!

  94. I am trying to do something much simpler. I would like to count the number of different items in a list and just by specifying the column as a range. Can this be done?

    1. The closest I have got is

      =SUMPRODUCT(1/COUNTIF('Sheet1'!A1:A8000,'Sheet1'!A1:A8593))

      Where there are 8000 lines in the spreadsheet. However if I put in 8001, I get a divide by zero error. It would also be preferable if I could ignore the top line as it is headings but I can jus -1 if needed

  95. Hi,

    Please help
    I have data this

    Folder Pax
    TU1 2
    TU1 3
    TU2 1
    TU2 5
    TU3 6
    TU3 1

    And I want
    Folder Pax
    TU1 5
    TU2 6
    TU3 7

  96. Hi - I'm trying to compare two worksheets to see if an email address on sheet 1 is in a range of cells in Column A in sheet 2. I've tried using vlookup and ifcount formulas but neither are working. I removed all formatting from both sheets and I'm using Excel 2013. Can you please suggest a formula or point me in the right direction? Thank you!!

  97. I want to count how many names appear in range C5:C11 from a list of names that I have. That list I use in drop down menus in the range C5:C11. My names are Reid, Tyrone, Chris, and Paul. I can select those names from the drop down menu. I want to count how many names from the list are in that range. I tried countifs with not sucess

    1. Hello Omar,

      You can use one of the following formulas:

      =countif($C$1:$C$5," Reid") or =countif($C$1:$C$5,F2)

      Where cell F2 contains the name Reid.

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

    Example (cells N2:N9):

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

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

    Thanks

    Matt

    1. Hello Matt,

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

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

  99. Hi there-
    I was wondering if I can use the COUNTIF function with a very specific conditional test. I have a set of data by date where for one date in column A, there might be 4 corresponding numbers in column B. Example:

    9/8/2013 43
    9/8/2013 45
    9/8/2013 36

    Is there a way to use COUNTIF to only count the appearance of a number greater than x once per date? For the above, I would want it to only count once if the criteria were ">35".

    Thanks,

    Steve

    1. Hi Steve,

      Since you want to count with two conditions, you have to use COUNTIFS rather than COUNTIF:

      =COUNTIFS(A:A, "9/8/2013", B:B, ">35")

  100. Hi Lydia,

    Your second comment made the task clearer.

    Since you have to sum by several conditions you will have to use the COUNTIFS function rather than COUNTIF. The formula can be as follows

    =COUNTIFS(A:A, F2&"*", B:B, F3, C:C, ">="&F4, C:C, "<="&F5)

    Where:

    F2 - the cell containing either N or M
    F3 - the cell with "New Birth" etc.
    F4 - the sell with the "start date", e.g. 1/4/14
    F5 - the cell with the "end date", e.g. 30/6/14

    Naturally, you can put the above conditions directly into the formula, but in this case you will have to re-write it for each set of conditions.

    You can also use the above formula in another worksheet, by adding the worksheet's name before the ranges, like this:

    =COUNTIFS([Book1.xlsx]Sheet1!$A:$A,F2, [Book1.xlsx]Sheet1!B:B,F3, [Book1.xlsx]Sheet1!C:C,">="&F4, [Book1.xlsx]Sheet1!C:C,"<="&F5)

    1. Thank you so much Svetlana, you are a a life saver, thank you so much for your time. I am trying the first part then I will venture on to the answers going in to another worksheet. But I am guessing or hoping IO can use the paste link to do this! Thanks again

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