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

  1. Hi,

    I have a problem for the last criteria. the criteria should be the value of AO is less than AJ. below is my code.

    Sheets("Visual Data (materials)").Cells(33, i) = Application.WorksheetFunction.SumIfs(Sheets("Raw Data (Materials)").Range("AS:AS"), Sheets("Raw Data (Materials)").Range("C:C"), "GEN", Sheets("Raw Data (Materials)").Range("AK:AK"), ">=1/1/" & Sheets("Visual Data (materials)").Cells(32, i), Sheets("Raw Data (Materials)").Range("AK:AK"), "<=12/31/" & Sheets("Visual Data (materials)").Cells(32, i), Sheets("Raw Data (Materials)").Range("AO:AO"), Sheets("Raw Data (Materials)").Range("AO:AO") & "<" & Sheets("Raw Data (Materials)").Range("AJ:AJ"))

    1. Hello!
      It is very difficult to understand a formula that contains unique references to your workbook worksheets. Hence, I cannot check its work, sorry.

      1. my question is actually, how can I include a criteria for countifs where values in range A:A is less than range B:B

        1. Hi,
          The formula will work. If these are ranges for criteria, then only data for which the conditions are met in two ranges at the same time will be counted.

  2. My question is simple but I don't know what formula to use to make it happen. I'm a quilter. There are a number of squares I get in a package of fabric squares to sew together to make a quilt. I would like a formula that I can put in the number of squares I have in a certain design and have it tell me how many different combinations of rows and columns I could have. Then show me in a diagram.

    This kind of formula is beyond my skill level. So if anyone would like to figure this out for me, I would definitely appreciate it!! Thanks!!

  3. Hi!
    How do I calculate a count of cells, omitting only the duplicates. that is, if there are 10 entries, and 2 of them appear twice, it should return an answer of 8 (not 6 or 4)?
    thank you!

    1. Hello!
      You can learn more about counting unique values in Excel in this article on our blog.
      I hope I answered your question. If something is still unclear, please feel free to ask.

  4. How do I insert a VLOOKUP as the criteria
    I tried but it keeps failing

    For example :-
    =COUNTIF($G5:DR5,"<VLOOKUP(A5,Codes!$A:$E,4,0)")

    1. Hello!
      It is not possible to insert VLOOKUP as a less than criterion in the COUNTIFS function.
      I recommend using something like this formula

      =SUM(--($G5:$DR5<VLOOKUP($A$5,Codes!$A:$E,4,FALSE)))

      I hope it’ll be helpful.

      1. Thank you :)
        But i did figure out to insert a VLOOKUP as a criteria and it worked

        =COUNTIF($G5:DR5,"<"&VLOOKUP(A5,Codes!$A:$E,4,0))

  5. The formula that I'm using is =COUNT(E143:E419)+COUNT(G143:G419) for the entry's and the =COUNTIFS(F143:F419;">=8:00")+COUNT(H143:H419) and in the result cell is =MAX(0;N1-O1) but it limits the leaving to 8:00 am, do you have a solution for this? Sorry for the bad english

    1. Hello!
      It is difficult to give advice without seeing your data. But perhaps the instruction will be useful to you - COUNTIF formulas for dates.
      Also, I do not know in what format your time is written. But you need to use the TIME function. Instead of ”>=8:00″, use >TIME(8,0,0). I suggest you read this article.

      1. Thank you, I will take a look

  6. Hi

    I need to count the number is cars that are present inside a parking lot. The trick is that sometimes some cars enter the day before and the record is for a single day. I need to count only the entry's but not when it leaves, but I still need to register the hour he leaves. So one camp as the license plate and then the entry hour and the leaving hour, I need to count the leaving hour but only if he has a entry ou else ignore or return a 0 but still count all the others cars inside!

  7. How can one count the number of cells in column A in which the values are greater than the corresponding values in column B? What Excel command should I use? I tried sumproduct but it isn't applicable as it adds up the actual values whereas I only want to know how many cells in A have values greater than B. Thanks.

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =SUM(--(A1:A20>B1:B20))

      Hope this is what you need.

  8. hi , I really need help , is there a way that I can count text that is in a range but not part of my "List" for example i had people fill a survey one of the qeustions had multiple checkboxes , the one checkbox was open awnser so i want to count the awnsers that are not in the pre specified checkboxes the [] other:_____ text I hope that this makes some sense to someone

    1. Hello!
      Without seeing your data, I cannot write a formula for you. But please reread the article above, it covers your case completely.

  9. I am doing a COUNTIF formula =COUNTIF($C$5:$C$19,"ben*"), but I need to copy the formula to three other cells. When I copy them, the exact same formula comes up for each line, but I need the "ben*" to change each time to a different name (cap, cat, hun). How do I get my formula to recognize that it needs to change without doing it manually?

    1. Hello!
      Instead of “ben *”, write a reference to the cell with this value in the formula. Like this:

      =COUNTIF($C$5:$C$19,D1)

      When you copy a formula, the link will change to a different cell.

  10. How do I count those records where Column X is not blank AND Column Y is blank? For example, Column X is "Drafts Uploaded" and Column Y is "Shipped." I need to count those record for which drafts were uploaded (Column X is not blank) but have NOT been shipped (Column Y is blank).

  11. what a formula be to count a range of blank cells if filtered.

    Ex. have a list, only want to count blank cells. If i only want the blank cells when i filter NY, how can i get it to only count the blank cells when i filter

  12. I have a lengthy list of names with dollars tied to purchases to each of those names. I am attempting to add up the totals for each of the individuals. Some have 3 lines, some have 1 and others have 5. How would I go about getting this to work?

    1. Hello!
      Unfortunately, without seeing your data it hard to give you advice. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

  13. How do you use countif when the cells you want to count have a return value in the Dated format example below.
    4 Yrs, 11 Mths & 8 Days
    The above shows length of service the formula used is:
    =IFERROR(IF(E6="","",DATEDIF(E6,$C$2,"y") & " Yrs, " & DATEDIF(E6,$C$2,"ym") & " Mths & " & DATEDIF(E6,$C$2,"md") & " Days "),"")

    Now I want to count how many colleagues have a length of service of more that 3 yrs. Is that possible?

    Thanks

    Phil

    1. Hello!
      You can count the number of dates for which the difference is more than 36 months:

      =SUM(--(DATEDIF($E$1:$E$10,$C$2,"m")>36))

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  14. I want to count alternate column value. for example i have column A-B-C-D but i need only to count column A & C Value.Please help me to know how i can do by formula.

    1. Hello!
      You did not say by what criteria you will count. Therefore, I cannot give an exact recommendation. However, you can add the two COUNTIF functions. In the first you count by column A, in the second you count by column C. Or you can use the COUNTIFS function. Read more here.

  15. Help me, how to calculate duplicate value for below range? i notice when i use this coding, repeated values such as 93 twice, not counted.
    =COUNTIFS(Z5:Z14,">=90",Z5:Z14,"<=94")

    Thanks in advance

  16. I want to count the no of cell with a particular value in continuation and break the counting is that value changes in a cell. which formula I have to use ?
    SEGMENT NAME 20Jul 21Jul 22Jul 23Jul 24Jul 25Jul 26Jul 27Jul 28Jul 29Jul Desired count reslut (Count <1)
    GOMADBAMANN3 100.00 96.61 76.37 19.06 9.25 0.00 90.24 0.00 0.72 0.00 2
    GOMADBAMANN1 100.00 96.07 76.37 20.99 39.14 86.97 99.94 0.00 76.60 0.00 1
    GOMADBAMANN2 100.00 96.76 76.37 15.67 0.04 52.55 0.00 82.61 78.17 0.00 1
    GOKILKILLIS3 100.00 99.97 100.00 100.00 100.00 97.25 0.00 73.75 33.04 0.00 1
    GOKILKILLIS4 100.00 99.97 100.00 100.00 100.00 97.22 0.00 75.06 33.03 0.00 1
    GOPARMADHAI1 0.00 36.65 19.06 0.00 0.00 0.00 0.00 0.00 0.00 0.00 7
    GOPARMADHAI2 0.00 36.65 19.06 0.00 0.00 0.00 0.00 0.00 0.00 0.00 7
    GOPARMADHAI3 0.00 36.65 19.06 0.00 0.00 0.00 0.00 0.00 0.00 0.00 7
    GOAMRAMRMAS3 100.00 100.00 100.00 100.00 100.00 0.00 0.00 100.00 34.87 0.00 1
    GOJADJADAUD1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 10
    GOJADJADAUD2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 10
    GOJADJADAUD3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 10
    GOHINHINDXX2 99.65 99.24 99.18 99.53 5.48 56.80 99.02 98.91 41.28 0.00 1
    GOBELBELA1X1 0.31 0.00 0.00 0.91 0.58 0.11 0.80 2.67 0.00 0.00 2
    GOFIRSADABA1 99.74 99.66 99.58 99.85 99.74 0.00 0.00 100.00 100.00 0.00 1
    GOFIRSADABA2 99.74 99.65 99.58 99.92 99.74 0.00 0.00 100.00 100.00 0.00 1

  17. 0100000037 288.00 288.00 PCS TRUE
    0100000037 234.00 234.00 PCS TRUE
    0100000037 384.00 384.00 PCS TRUE
    0100000038 36.00 36.00 PCS TRUE
    0100000038 24.00 24.00 PCS TRUE
    0100000041 120.00 120.00 PCS TRUE
    0100000041 120.00 120.00 PCS TRUE
    0100000044 168.00 168.00 PCS TRUE
    0100000044 108.00 108.00 PCS TRUE
    0100000045 144.00 144.00 PCS TRUE
    0100000047 240.00 240.00 PCS TRUE
    What formula should i use to count every cell ?
    for example 0100000037 this Code is showing in 3 cells so what i ll do to calculate in cell a1=1 a2=2 a3=3 and so and so ?

  18. The "COUNTIF - COUNTIF to count numbers within a range" section is clearly wrong. If you just manually count the matches on the list, there are a total of 5.

    Not sure if your understanding of the function is wrong or just your visual aid, but since I can't get this to work based on your instruction I suspect it is the former.

    1. Hello Charles!
      You're right - the formula does not take into account the number 15. Thank you for your attention. I fixed everything

  19. Count in a single formula total A or B Or C
    where =COUNTIF(C1,"A") it can count only A

  20. Hi there,

    For my homework assignment I'm supposed to be using countif for data on earnings for various companies. In one column she wants me to be able to have each value say if it's over $1billion or below, and if it's below, by how much. I am confused on how to do this in one column. She also wants it to display text of my choosing, which I would say "Above" or "below"

  21. Hi i have a spreadsheet Songs and i need to use Simplex criteria to get the names of the row, which formula do i use?

    1. Hello!
      Please describe your problem in more detail. Write an example of the source data and the result you want to get. It’ll help me understand it better and find a solution for you. Thank you.

  22. Hi there! I am trying to combine these formulas so that both criteria must be true for it to be counted however as my ranges are different sizes this is problematic with the countif function. Is there a way around this?
    These are the two formulas which need to be combined:

    =COUNTIF('Volunteer Call Handler Form APRIL'!Q:V,"XXXXXXX - recent")
    AND
    COUNTIF('Volunteer Call Handler Form APRIL'!G:G,"Inbound")

    1. Hello Tilly!
      You can use the COUNTIFS function. But уach additional range must have the same number of rows and columns as the criteria_range1 argument. The ranges do not have to be adjacent to each other. Read about it here.

  23. Hi, I want to count those items which have 3 letters + 4 numbers from below, how to count it please?

    ABC2001
    ABC2002
    ABC2003
    ABC2004
    ABC2005
    ABC2006
    ABC2007
    ABC2008
    ABC20A
    ABC20B
    ABC20C
    ABC2009
    ABC2010
    ABC2011

    1. Hello,
      Please try the following formula:

      =SUMPRODUCT(--NOT(ISNUMBER(--LEFT(C1:C14,1))), --NOT(ISNUMBER(--LEFT(C1:C14,2))), --NOT(ISNUMBER(--LEFT(C1:C14,1))), --ISNUMBER(--RIGHT(C1:C14,1)), --ISNUMBER(--RIGHT(C1:C14,2)), --ISNUMBER(--RIGHT(C1:C14,3)), --ISNUMBER(--RIGHT(C1:C14,4)))

      I hope it’ll be helpful.

  24. Sorry, not sure what happened to what I typed above. Edited:
    Hello, I am trying to count the number of cells within a range of cells that fall between two values. So my values which range between 1000 and 50000 are located in cells I2 through I21. I put a value of 5000 in N4 and 25000 in O4. I want to count how many times the values in cells I2 through I21 fall between 5000 and 25000.

    When I use this formula: =COUNTIF(I2:I21,">="&N4)+COUNTIF(I2:I21,"="&N4) I get 27.

    When I use =COUNTIF(I2:I21,">="&N4)+COUNTIF(I2:I21,"="&N4) I get 3.

    I can count the cells and see that the answer should be 7. 7 out of 20 cells fall between 5000 and 25000.

    Thank you.

  25. Hello, I am trying to count the number of cells within a range of cells that fall between two values. So my values which range between 1000 and 50000 are located in cells I2 through I21. I put a value of 5000 in N4 and 25000 in O4. I want to count how many times the values in cells I2 through I21 fall between 5000 and 25000.

    When I use this formula: =COUNTIF(I2:I21,">="&N4)+COUNTIF(I2:I21,"="&N4)-COUNTIF(I2:I21,"<="&O4) I get 3. This is also incorrect because I can count the cells and see that the answer should be 7. 7 out of 20 cells fall between 5000 and 25000.

    Thank you.

    1. Hello Adina!
      If I understand your task correctly, maybe the following formula should work for you:

      =COUNTIF(I2:I21," >= "&N4)-COUNTIF(I2:I21," > "&O4)

      or

      =SUMPRODUCT(--(I2:I21>=N4),--(I2:I21<=O4))

      If there is anything else I can help you with, please let me know.

  26. Hello,
    I am trying to count the instances where a particular string of text is found in column A, and the date in column D is within a specific range. Could anyone tell me why this isn't working?
    =COUNTIFS(Datasheet!A:A,"*theme*",Datasheet!D:D,">=6/1/2019", Datasheet!D:D,"<=6/30/2019")
    Thank you!

    1. Hello Madeline!
      Please try the following formula:

      =COUNTIFS(A1:A40,"*theme*", D1:D40,">="&DATE(2019,6,1), D1:D40,"<="&DATE(2019,6,30))

      Hope you’ll find this information helpful.

  27. Hi i just seek your assistance, i am trying to count the number of request i've send to a specific person in a day and also the number of answer i receive with them i day also. Here's my problem my formula doesn't count right. Here's my formula in Sheet1 (=COUNTIFS(March!O:O,">=" & Summary!B5,March!O:O,"<=" & Summary!B5+1)) details are from Sheet2 with a date and time format for March. Summary!B5 means the day today or next day. Hope you can assist me thanks.

  28. Hi there.

    I have data with students performance in a particular classroom.
    Data Columns: Names, Age, Gender, class/grade, Attendence, Performance status, performance status update.

    Options of performance status / update are: poor, good, excellent.

    I want to run some queries on this data in a summary table giving the following information:
    1. Count Attendance by: Age, Gender and Class/Grade
    2. Count Performance by: Age, Gender, Class/Grade whereby I count Performance Update when both Performance Status and Update have values and count performance status when Update is blank.

    Please help. I was thinking of using class/grade as a filter for the summary table such that I can switch between classes easily as well as having overall school summary

  29. This is FABULOUS! Thanks!

  30. I am trying to count the number of shifts a person works between a date range, but the shifts are on a 4 on, 4 off schedule.
    In a separate tab, I have the start & end dates of each of the shifts till the end of the year. How do I tell it to count the number of days that someone would work, on this shift, given their start & end date of employment.

  31. The `COUNTIF(range,""&"")` will fail in situation like this: A1 = "abcd", A2 = "1234", A3 = IF(COUNTA(A1)>0, "", A1). The result will be 3 which is wrong because A3 is technically a blank cell.

  32. Hello, I really hope someone can help me,m this has come up a few times at work and we really cannot figure it out!! we want a formula for 2 columns, so.. if column A says 'apple' to count it, but only count it if column B is greater than 60.

    I hope someone can help me!!

  33. c column is differnt date and the f column is number..
    i want to search how many blanks cell of column c date..
    example
    c column f column]
    07-02-2020 23565
    07-02-2020 2454
    07-02-2020
    08-02-2020
    08-02-2020 251454
    08-02-2020

    07 february 01 cell empty and 08 february 02 cell

  34. I would like to count values based on dates with 2 criteria's.
    I need the count of unique tickets worked by agent on single day
    (If same ticket is repeated multiple times on single day it should count as one but if same tickets is worked on different date then it should increment by 1.
    Please help.
    The formula used here is counting unique tickets for whole month
    Formula used: 2 attempts made but same result
    1st attempt : =SUM(IF((H6=$H$6:$H$27)*($E$6:$E$27<=DATE(2020, 1, 31)), 1/COUNTIFS($H$6:$H$27, H6, $F$6:$F$27, $F$6:$F$27, $E$6:$E$27, "<="&DATE(2020, 1, 31)), ""))
    2nd attempt: =SUM(--(FREQUENCY(IF(H6=$H$6:$H$25, COUNTIF($F$6:$F$25, "<"&$F$6:$F$25), ""), (COUNTIF($F$6:$F$25, "0))
    (wrong value)
    Date TICKETS DISPATCHED ASSIGNEE Total ticket Correct value(to be)
    2020-01-24 INC123 Pretty Deena Mathew 2 3
    2020-01-24 INC123 Pretty Deena Mathew 2 3
    2020-01-25 INC123 Pretty Deena Mathew 2 3
    2020-01-24 inc1234 Pretty Deena Mathew 2 3
    2020-01-25 ritm012 Rachit Sharma 1 1
    Here, first 2 rows value should count 2 as same ticket is worked on single day by one agent,
    3rd row should be counting 3 as same ticket is worked on NEXT DAY..

    This is very important to resolve.
    Please assist
    Thanks in advance

  35. What if I'm counting less than / greater than / equal to between times? I have an expected arrival time (column D) and an actual arrival time (column I) listed in HH:MM:SS time and I need to know how many groups arrived before after or exactly when they expected.

    Bonus points if I can add another criteria to account for the type of visit ( SGV, Program*, Group)

  36. Hello,
    I have a table with a range of cells in one column that I want to count from one cell.
    i.e Column P cells 1 through 10, each cell represents a repair number "P1 = Repair 1, P2 = Repair 2 etc etc.
    In this range I want all cells to count if the repair numbers are in one other cell ie if cell Q1 has 1, 2, 3, 4, 5, 6 typed in it I would like cell P1, P2, P3, P4, P5, P6 to each show 1. Then if cell Q2 has the same 1 through 6 typed same as Q1 I want cells P1 through P6 to each show 2.
    Hope you can help
    Gavin.

  37. A1 AHU
    A2 AHU
    A3 Boiler
    A4 Boiler
    A5 Boiler
    A6 Chiller
    A7 Chiller
    A8 Chiller
    A9 Chiller
    A10 Chiller

    How to count that How much AHU,Chiller & Boiler in my sheet. I Have 1000 Data in sheet.

    1. Hi there! If you require to know how many of each "AHU", "Boiler" and "Chiller" are in your column, then I hope you find this useful.

      In cell A1, let's assume there is a "Product Type" header. In cell B2, create a "AHU" header. In cell C2, create a "Boiler" header. In cell D2, create a "Chiller" header. The headers in cells B2, C2 and D2 will be your target cell references.

      Let's assume "AHU" is in cell A2, "AHU" is in cell A3, "Boiler" is in cell A4, etc.

      To count the total number of "AHU" in column A, the formula is: =COUNTIF(A:A,$B$1) the result should be 2. To count the total number of "Boiler" in column A, the formula is: =COUNTIF(A:A,$C$1) - the result should be 3. To count the total number of "Chiller" in column A, the formula is: =COUNTIF(A:A,$D$1) - the result should be 5.

  38. I have 4 employees that maintain multiple properties monthly. I need a formula that will calculate how many properties are still needing completed per employee. I have the employees in one column and dates completed in a different column. I need it to count the blanks for each individual employee.

  39. This is wrong:
    =COUNTIF(B2:B9,">5")-COUNTIF(B2:B9,">=15")
    It should be:
    =COUNTIF(B2:B9,">5")-COUNTIF(B2:B9,"<=15")
    Please update.

    1. Hi John,

      Just test both formulas on any sample data, and you will see which one is correct :)

  40. I would like to know the answer:
    How to count the number of leaves in a year using a formula. Like how to capture those dates that I have taken as leave.

  41. Thank you for being very helpful, I am looking for an answer to my question, Is there a formula to this situation?
    Name Amt
    AA 40
    DD 45
    AA 48
    GG
    SS 54
    AA 67
    GG 85
    AA
    AA 12
    KK
    AA 6
    LL 15
    AA
    QQ 4
    Count all # of "AA" if there is a value in column "Amount"

    1. Hello Harry,
      If your task is to count all the AA entrances that have any amount, please use the formula below:
      =SUMPRODUCT(($A$2:$A$15="AA")*($B$2:$B$15<>""))

      If this is not exactly what you need and you'd like to sum these amounts, here is the formula for you:
      =SUMPRODUCT(($A$2:$A$15="AA")*($B$2:$B$15))

  42. Hi Team
    I have a spreadsheet which includes data on customer enquiries including customer ID, enquiry type, date, referral source etc. To count the enquiries by business area (made up of different referral sources) in a given month, I am using the following formula:
    =SUMPRODUCT(COUNTIFS('MYREPORT'!$H:$H,"ENQUIRY TYPE A",'MY REPORT'!$B:$B,CATEGORYLIST!$H$2:$H$27,Enquiry_Date,">="&P2,Enquiry_Date,"<"&EDATE(P2,1)))
    P2 in the above is the month I am totalling enquiries for. The reason for the 'CATEGORY LIST' part is so that I can group various different referral sources (people) into one reporting point e.g. 'TEAM A'.

    My problem:
    I need to be able to count a different enquiry type, 'ENQUIRY TYPE B' in the same way, but want to exclude any occurrences where the same client has already had an 'ENQUIRY TYPE A'. I am finding this difficult especially as my initial formula above does not refer to the unique client ID field and in order to exclude clients who already have had an 'ENQUIRY TYPE A', I am presuming it needs to.

    Many thanks for any help you can give

  43. Hello, I have a dynamic data set that is being changed daily (new rows being added to the top) and I want to have a unique ID created for each distinct set on "today's" date.
    so for example the data-set may look like the following
    nov 2, 2019 5704
    nov 2, 2019 5704
    nov 2, 2019 5705
    nov 2, 2019 5705
    nov 2, 2019 5706
    nov 1, 2019 3416
    nov 1, 2019 3416
    nov 1, 2019 5704
    oct 31, 2019 5705
    oct 31, 2019 5705

    So on the date of Nov 2, 2019 I have 3 distinct rows. I will be adding new rows to the top every day by using a macro so the data range should extend down in order to continue capturing all the data. My preferred range would be something like A:2 to A:3000

    as of right now I have a formula that tells me how many distinct sets I have in the entire data-set but I dont want that I want it to be able to look at the entire data set and only tell me how many distinct sets there are on today's date. I have tried a couple of things and I cant seem to figure it out. Here is the formula I currently have
    =SUM(IF("range of column 1""",1/COUNTIFS("range of column 1","range of column 1","range of column 2","range of column 2"), 0))

    Extra Note:
    If I were to apply this formula to the data set i have above it would give me a result of "6"

  44. Hello. I think I'm on the right track and the COUNTIF function is what I need.
    Say I have a table in column C with some blank cells, and I want to add an adjacent column D but omit the values in column D which are a next to a blank cell in column C. Would I use the COUNTIF or SUMIF function? the cells in column D need to be summed up. Also, can you help me out with the syntax?

    Thanks

    1. Yes you can, but you'd want to use COUNTIFS as this allows for multiple sets of criteria.
      something along the lines of:
      if your using numbers then
      =COUNTIFS(C1:C10,">0",D1:D10,">0")
      if your using text
      =COUNTIFS(C1:C10,">""",D1:D10,">""")

  45. Hi I wanna count how many awaiting report and good run column A with a criteria of today-2days. I'm having trouble building a formula please. Thank you

  46. Dear, Please solve my Problem. i try to develop formula of COUNT, but not count Duplicate value.

    Inv. No.
    18
    18
    20
    21
    627
    628
    628
    630
    631
    632
    632

  47. You have to sort out your days of the year against time. In column A sort the Dates and in column B sort the time.
    Column C
    ={INDEX($A$2:$A$1000,MATCH(0,COUNTIF($C$2:C2,$A$2:$A$1000),0))}
    Column D
    {INDEX($B$2:$B$1000,MATCH(0,COUNTIF($D$2:D2,$B$2:$B$1000),0))}

    Column to get the above sorted date and time count
    ={COUNTIF(IF(($C$2:$C$1000=$C2)+($D$2:$D$1000=$D2)=2,1),1)

    =COUNTIF(IF((B2:B1000=Date(2019,07,02)+(C2:C1000="15:30")=2;1);1)

  48. I have the following problem. I want to be able to track the number of leads I got on my website during the year, or perhaps even over several years of time and plot this in a graph. I have a column with the date I got the lead and a column with the time I got it.

    I can use count if to get a column with the number of times a certain data appears. I would like to do this for every date in the year. For example 20 leads on January the first, 25 on January the second, etc. etc.

    How do I do this without having to input every single date of the year?

  49. 1st column salesman , 2nd product name, 3rd quantity of sale,,,salesman name & products r repeated,,,so it is possible that with countif formula salesman sales same product many times & how much sales? Results should be sum not count 5tine,,,result example - 1st time 10, 2nd time 14, 3rd time 7= result should be 31 not 3

  50. Please help,
    In my Data, 1st column Date (reflecting 120 days)2nd column repeated names against various dates of 1st column (like A, B,C,D etc). how to count number of A,B,C,D between two dates? please help

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