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

  1. Hi,
    I need to count the amount of profit-making enterprises:

    Проект* Выручка Себестоимость
    SAMA-001_ALFA 100 90
    SAMA-003_BETA 120 200
    ...

    THANK YOU

    1. Count #projects if = Выручка>Сеьестоимость

      1. Hi,

        Supposing that "Выручка" is column B and "Себестоимость" is column C, you can use the following array formula:

        =SUM(--(B2:B10>C2:C10))

        Remember to press Ctrl + Shift + Enter to enter the array formula correctly.

  2. Can you please give me solution for my earlier email?

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

  4. Hi Svetlana,

    I have some data like this in a column. If am giving formula (=COUNTIF(A1:A2000, A1)) to count the number of repetition of each number its not getting counted properly. Its taking into con sideration only few entries. May be coz character are more than 19. Is there any way to count the repetitions if the number of character in a cell is more than 19.

    89XXXXXXXXXXXXXXX44
    89XXXXXXXXXXXXXXX36
    89XXXXXXXXXXXXXXX28
    89XXXXXXXXXXXXXXX10
    89XXXXXXXXXXXXXXX02

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

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

  7. Hi,
    I know how to do this using PIVOT, but I have a non power user that will use a template sheet in excel and I'm trying to help him to get a count duplicates cases based on a condition.

    Case Cause Dealer
    447839 L CA
    447839 P CA
    447839 S CA
    448387 L CA
    448387 P CA
    447646 L PH
    447646 P PH
    447647 L PH
    447647 P PH
    447648 L PH
    447648 P PH

    In above example CA have 2 cases and PH have 3 cases. Using PIVOT is easy to get the result rather then calculating through transactional data but the user isn't PIVOT oriented and I tried hard to trained.

    How I can achieve above summary results using a function/complex formula.

    Anticipate thanks,

    Jose

  8. Hello,

    I have the following data:

    Column A Column B Column C
    Estimate Percentage Percentage Expected
    (days) Complete Remaining Result

    0 0 Not Required
    5 25 3.25
    1 75 .25
    Not Required 0 Not Required

    So, those fields that are not a number and are equal to zero should not be calculated, but for the formula used to calculate "Percentage Remaining", it is: Column A * (Column B /100)

    So, my question is: How do I exclude the calculations that are zeros and are not numbers in Column A and Column B?

  9. Hi, I have a spread sheet where I have a resident name in column A and a hall number in column B. There are multiple residents on the same hall, and sometimes the resident is listed more than once. I want to count the number of times a hall is listed, but no duplicates(i.e Joe 100
    Bill 200
    Joe 100
    Chris 300
    Maple 100. So I would want to find out how many times 100 is involved without the duplicate so the count should be 2. Can you help me? thanks,Joe

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

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

  12. Svetlana,

    I'm trying to make a formula that does not count any cell that is Blank or 0. What am I missing here? "=-COUNTIF(B5:F5,B6:F6,"<=0"),(B5*B6+C5*C6+D5*D6+E5*E6*F5*F6)/G5" The B5..F6 cells could be blank or 0, but I just want a simple formula that does provides the weighted average.

    Thank You!

    Johan Bolle

  13. I have summation formulas in column Z and 2 cells with a value > 0
    When I use the COUNTIF(Z5:Z108,">0" ) it correctly answers 2
    How can I combine that with the question "How many are SHOWN that have a value > 0 somewhat like =Subtotal(109,Z5:Z108)

    Unsuccessfully tried
    SUBTOTAL(109,COUNTIF(Z5:Z108,">0")) and
    COUNTIF(SUBTOTAL(109,Z5:Z108),">0")

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

  15. I am trying to keep track of attendance of employees for the current month. I have a formula that will calculate the last 30 days for the attendance code, but instead of last 30 days I would like for current month only:

    =COUNTIFS(Attendance!$B:$B,$B5,Attendance!$E:$E,"CIO - CI OUT",Attendance!$C:$C,">"&(TODAY()-30))

    I have 2 separate sheets - Attendance sheet which is the sheet I log attendance to, and the other sheet where I am placing my formulas on. Here is a breakdown of everything from that formula above

    Counts for that employee if it meets all of the following criteria:
    **Attendance!$B:$B,$B5 >> Employee Name (want to count specific employee)
    **Attendance!$E:$E,"CIO - CI OUT" >> Attendance code (want to count specific attendance code)
    **Attendance!$C:$C,">"&(TODAY()-30) >> THIS IS WHAT I NEED TO CHANGE. I need to change instead of being last 30 days, to be for the CURRENT month only.

    So today being in May, I want it to count how many times "John Doe" has had the Attendance code of "CIO - CI OUT" for THIS MONTH. It should reset next month to only include June automatically.

  16. 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,"~?")

  17. Hi Svetlana,

    I have an excel table to keep a running balance of the decreasing loan amount, as payments are made (see excerpt from table below).
    Currently, the formula in column D is =SUM(D2,C3) & the formula in column E is =E2-D3
    The problem with this is that is when I continue the formula's all the way down to #21 on the list; the $150 in D continues all the was down the list, & the $4,350 in E does the same. It stays that way until I add a new payment to column C; and then the new total continues down the list.
    I don't want anything showing in Columns D & E until another payment is made. I want the formula's in there; so the balance automatically adjusts when I enter a payment, but I don't want any value showing if no payment has been made.
    I thought I could accomplish this by using a COUNTIF formula.
    Something like: If C is greater than zero, then =SUM(D3,C4)& =E3-D4.
    I'm not sure how to set the up.
    Can you help me with that? Any help would be very much appreciated.

    C D E
    $ AMOUNT of PMT BALANCE PD TO DATE BALANCE OWING
    $0.00 $0.00 $4,500.00
    $150.00 $150.00 $4,350.00

  18. Dear Sir/madam,

    Please help.

    I have a range of codes with some are duplicate in one column. I would like to count the code but for those are duplicate to be counted only one time.

    Veasna

  19. i want to know how calculate spreadsheets
    my no is this 109|110
    109|110
    109|110

    how many times 109 in this range

  20. I have a spreadsheets that tracks the schedule of my sales rep's from week to week. I have a column set up to count how many total appointments they have per day. In this column I use the formula =COUNTIF(tblMonday[@[9:00 AM]:[5:00 PM]],"*") so it reads all addresses as a count. I have another column set up to count only the warranty appointments for that day. In that column, I am using the formula =COUNTIF(tblMonday[@[9:00 AM]:[5:00 PM]],"(W)*") to recognize that when I type (W) in front of the address, it is a warranty call and will go just to that column. I would like to add a third column that counts only the addresses that have no (W) in front. Please... send help. :-)

  21. I have a table with numbers in Columns F, G, H, I, and J. There is an identifier in Column B.

    I need the average of all numbers in Columns F, G, H, I, and J where the identifier in Column B = A14 (i.e. the value in A14).

    I tried the following:

    =AVERAGEIFS('Hours-Backup'!F3:J1048576,'Hours-Backup'!B:B,'Group Dashboard'!A14)

    This results in a #VALUE! error.

    I can't figure out what the problem is. Any help would be appreciated.

    Ultimately, I want to make it so that the function ignores all cells that are non-numeric or blank. In other words, I don't want it to assume that blank cells are 0 for averaging purposes.

    Thanks!

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

  23. Hello Svetlana,

    I need help with the following problem. I have a column with 4 different values:

    Column A
    39
    19
    20
    0
    20
    19
    19
    19
    0
    39
    19
    0
    39
    19
    39
    0
    0
    0
    19
    39
    20
    0
    20

    I need a formula that will count the number of appearances of the number 0 since the last time the number 39 has appeared. Numbers 19 and 20 are not important, but they have to be there. Basically it counts the number of times the lowest value in the column has appeared since the last appearance of the greatest value in the column. The formula will be put in column B and will have to show the following results:

    Column B

    0
    0
    0
    1
    1
    1
    1
    1
    2
    0
    0
    1
    0
    0
    0
    1
    2
    3
    3
    0
    0
    1
    1

    Is it possible?

    Thanks in advance!

  24. Hi Svetlana Cheusheva

    hope your doing well.. please help me out .. i have attached my worksheet with this mail.

    i want total value of cells A , B , & C to be calculated in Cell "D" automatically .

    please show me step by step..

    TERMINAL

    PRICE

    A

    1000

    B

    2000

    TICKET

    PRICE

    YES

    0

    NO

    150

    STAGGING

    PRICE

    STAGE-1

    510

    STAGE-2

    510

    PARKING

    0

    your support will be highly appreciated

  25. Hi Svetlana,

    I am trying to figure out how to formulate a countif function where I am trying to measure exposure on certain loans on any given date. I have a bunch of loans, the amount pertaining to that loan and the respective maturity/expiry date. Essentially, I am trying to measure peak exposure from today lookng forward over the next 5 years so that I can create a graph in excel illustrating exposure over time. I would like the graph to show me how much exposure I have to those loans in year 3 in quarter 4 and how many have been repaid.

    Do you have any idea how I might do so?

    Many thanks in advance,

    Ben

  26. Hey, great post, and thanks for the pointers! I have a basic timesheet for work and would like to count the days spent on each project. Are you able to tell me how I can redirect the condition to another cell? So that:

    =COUNTIF(timesheet!$C$4:$G$500,"project A")

    would become:

    =COUNTIF(timesheet!$C$4:$G$500,"cell reference that redirects to the beginning of the row, which is where the project is named")

    This way, I will be able to remove the need to enter the project name twice and the risk of an error due to billing days for project A against project B.

    Thanks!

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

  28. Hi Svetlana,

    Thank you for publishing this page and always being active to respond.

    I am stuck with a Problem :

    Query : Count of projects between 01/01/2015 and 01/31/2015 whose status is ABC OR XYZ.

    From your forum I could do it for Count of project Less than 01/31/2015 but that will include all 2014 and 2013 projects also.
    I used the following formula for this :
    =COUNTIFS(Details!D17:D33,"<01/31/2015", Details!E17:E33, "ABC")+COUNTIFS(Details!D17:D33,"<12/31/2014", Details!E17:E33, "XYZ")

    Could you please help out with date range for this.

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

  30. I want to know whether its possible to calculate and if yes what are the tags to find out whether the values have crossed the certain threshold and if it does can the names of whoever has crossed can be displayed

  31. Hi,

    I need to count the number of hours for midnight charge given the criteria to be 11pm to 6am.

    E.g.(1) Cell A1 9pm and cell B1 9am. the result for midnight hours should be 7hrs
    E.g.(2) Cell B1 12am and cell B2 10am. the result for midnight hours should be 6hrs.

    How should i input the formula?

    thank you very much.

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

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

  34. I am trying to count how may times Toronto plays a game in the U10 age group.

    Column A = Age group
    Column B = Home Team
    Column C = vs
    Column D = Away Team

    Age Group Team 1 Team 2
    U8 Moncton vs Halifax
    U9 Toronto vs Moncton
    U10 Moncton vs Halifax
    U12 Halifax vs Vancouver
    U8 Toronto vs Vancouver
    U9 Halifax vs Toronto
    U10 Toronto vs Vancouver
    U12 Vancouver vs Moncton
    U8 Vancouver vs Halifax
    U9 Toronto vs Moncton
    U10 Moncton vs Vancouver
    U12 Vancouver vs Moncton
    U8 Vancouver vs Toronto
    U9 Moncton vs Vancouver
    U10 Toronto vs Moncton
    U12 Halifax vs Fredericton

  35. if any cell in column b equals a certain text then count cells in column c

    post jan feb mar apr may
    admin 10 20
    admin 25 2
    asst manager 10 17
    asst manager 2 28
    bartender 21
    bartender 30
    captain 15 15
    captain 13 17
    hostess 10 15
    hostess 25 5
    manager 16 14
    manager 30
    manager 25 3
    manager 20 2
    supervisor 20 10
    supervisor 15 15
    waiter 25
    waiter 30
    waiter 20
    waiter 18
    waiter 10
    waiter 14 13
    waiter 20
    waiter 25
    waiter 28
    waiter 30
    waiter 15
    8 8 11 10 4

    admin 1
    asst manager 1
    bartender 1
    captain 0
    hostess 1
    manager 1
    supervisor 1
    waiter 2
    Total 8 0 0 0 0

  36. Hi,

    I want to format Column A of a sheet. Column A has names and in columns B:M are the 12 numbers (1-75) associated to each name. In Q1:Z30 I have up to (depending on the day) 300 random numbers 1-75. I need the name in Column A to turn Pink when 9 of the numbers match the numbers entered in Q1:Z30. Since there are up to 300 numbers entered in Q1:Z30, some numbers are entered more than once. Here is the Conditional Formatting formula that I am using that isn't working:

    =SUM(COUNTIF($B1:$M1,$Q$1:$Z$30))>=9

    The formula is counting each instance that it matches and adding those up, and when those 12 numbers have been matched 9 times it highlights the name in Column A. I need the formula to highlight when 9 of the numbers in B:M have been matched period, not how many times they have been matched.

    Thank you so much for your time!

  37. Hello,
    I need help with function Countif, so:
    I want to count two cells for example: if cell A1=1 or cell A2=1 so count as 1, if one of these cells equal 1, count as 1, if both cells equal 1, count 1 as well. How is it possible to do with function Countif or with other function?

    Thank you in advance

  38. I have two columns. I would like to count the the no. of values in the right column greater greater than the corresponding values in left column. How to accomplish this?

  39. Hi Svetlana - Hope you are fine, required small help from you, is there any formula to capture code from statment by using if formula. I have 34 Codes, need to capture below code if they come in different entries, Example for entry : XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX & /AC04/ XXXXXXXXXXXXXXXXXXX, in this case AC04 is code. want to capture below code by using if formula, they can start AC or AM or BE or AG or BT or MS or PY or RS.

    1 AC01
    2 AC02
    3 AC03
    4 AC04
    5 AC05
    6 AC06
    7 AM01
    8 AM02
    9 AM03
    10 AM04
    11 AM05
    12 AM06
    13 AM07
    14 AM08
    15 BE01
    16 BE02
    17 BE03
    18 BE04
    19 BE05
    20 AG01
    21 AG02
    22 DT01
    23 MS01
    24 PY01
    25 RF01
    26 RC01
    27 RC02
    28 RC03
    29 RC04
    30 RR01
    31 RR02
    32 RR03
    33 TM01
    34 X1!c2!n

    Please can you advise a formula for this.

    Thanks & Regards
    Ashish Baddar

  40. Hi there,

    Good day to you, i have a XLS with 2 Tab Aging and score card. I have different value for column "W" in aging tab and i would like calculate the below.

    1. to calculate the total for all the different texts which are not "abc" and with a specific aging day.

    ** Please Help**

    2. to calculate the total "abc" and with a specific aging day, which i mange to do it.

    =COUNTIFS(Aging!$W:$W,"abc",Aging!$B:$B,ScoreCard!J18,Aging!$N:$N,"<15")

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

  42. You're really great!!

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

  44. Hi, I have a workbook with about 6 worksheets.
    Each worksheet has to be filled with various values name age ....
    the last worksheet is a 'report worksheet' that has to pick up input data per month from the other worksheets, undergo some formulas and then display the result.
    This is all fine.

    My problem is the other worksheets are filled in on a daily basis on the same sheet over many months BUT the report has to dis-aggregate the data by months to be input into the report. n.b. There is a month column.

    Now if i have a formula such as =COUNTIF('SHEET1'!AF9:AF608,"FEMALE")
    how can i manipulate the formula into picking up the no. of 'females' in 'sheet1' in march only
    n.b. there is a seperate 'report worksheet' for each month.

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

  46. Hi there

    I am trying to find out a way to count the dates in multiple cell range : in My excel sheet

    30-Sep-14
    30-Dec-14
    30-Jul-14
    30-Jul-14
    30-Jul-14
    30-Jul-14
    30-Sep-14
    30-Aug-14
    30-Aug-14
    30-Dec-14
    30-Dec-14
    30-Dec-14
    7-Oct-1
    30-Sep-14
    30-Dec-14
    30-Dec-14
    30-Jul-14
    30-Dec-14
    7-Oct-14
    there are dates in column range O3:O46,L3:L47,R3:R51,U3:U51,X3:X50

    i am trying to get the count of date which fall in within particular months i.e. April 14, May 14,June 14.July 14.August 14,September 14.....

    i am using this formula but not getting any result, thanks in advance for any help.

    =COUNTIFS(O3:O46,L3:L47,R3:R51,U3:U51,X3:X50,">=1/1/2013", O3:O46,L3:L47,R3:R51,U3:U51,X3:X50,"<=31/1/2013")

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

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

  49. Hello!
    Im trying to count blank cells in a column in my spreadsheet, but it wants to count the whole sheet and not just the ones included in my table. How can i do it so it only counts blanks that are part of my table (they have other information in their rows). Any suggestions on how to do this?
    Thank you!
    Hailey

  50. Hi - hoping you can help.

    I need to count column B (which are various ID numbers), remove the duplicates but only count them if Column A says "BIP" and Column F says "Active"

    Is it posible to do this in one formula??

    Thanks heaps

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