Countif a cell is blank or dated before a date

Hi all

I'm pulling my hair out trying to figure out the correct way of writing a Countif a cell is either blank OR contains a date before a date referenced in a different column.

I've been playing around with =COUNTIF({DateClosed},OR(ISBLANK(@cell),DATE(@cell)<MonthStart@row)) but it's coming up as UNPARSEABLE.

Can anyone help?

Thanks

Sarah

Best Answers

Answers

  • s_mawer
    s_mawer ✭✭
    Answer ✓

    After typing that out, I think I fixed it! Should it be =COUNTIF({DateClosed},OR(ISBLANK(@cell),@cell<MonthStart@row)) ?

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    Hi Sarah

    If you need 2 criterion in a COUNTIF function, have you tried using =COUNTIFS()

    I am sorry, just been called into a conference call, have a play with COUNTIFS and see if that helps, I'll return after my call to see if you figured it out!

    I'd also play with ISDATE() which checks a cells value to see if there is a date in it.

    I'll be back later!

    Kind regards

    Debbie

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    @Paul Newcome I have come back to this and am experiementing. With my understanding of the need the solution that @s_mawer has come up with albeit great :) I'm not 100% sure it is working.

    When I recreate the scenario and use the final formula given:

    =COUNTIF({DateClosed},OR(ISBLANK(@cell),@cell<MonthStart@row))

    I don't get the response that I believe I should

    If we have 4 rows and I change {DateClosed} to DateClosed1:DateClosed4 and use the formula above in a result column I would expect the return value to countif row 1's DateClosed date is < row 1's MonthStart date (or if it is blank), then add that to a returned count from row 2's DateClosed date compared against MonthStart's row 2 date; then add that to a returned count from row 3's DateClosed date compared against MonthStart's row 3 date. etc. In my version of this (using the formula from the thread) the @cell part of the function is behaving and moveing relatively through the list of values, but the MonthStart@row part appears to fix the comparison date to just the date on the same row as the formula result.

    Paul - could you advise how to update the formula for the MonthStart value to move along with the DateClosed one?

    The formula in the highlighted cell is the same as s_mauer's one:

    =COUNTIF(DateClosed1:DateClosed4, OR(ISBLANK(@cell), @cell < MonthStart@row))

    But I would expect it to be returning a value of 3 not 2 as 3 of the entries from DateClosed are lower than their equivalent MonthStart. But only DateClosed1 and DateClosed3 are lower than MonthStart@row.

    It is late in the day here and I too, can't quickly come up with a right result! Can you help?

    Thanks

    Debbie

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Debbie Sawyer

    It's the @row reference. MonthStart@row.


    The formula doesn't compare the two columns row by row side by side, it is comparing the DateClosed column to the date in the MonthStart column for whatever row the formula is on. Based on your screenshot, the count is accurate.

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    @Paul Newcome

    Thanks for your response, Yes I agree that the @row bit is doing what you said, however, my point was that I don't think that is what the originator of the post wanted! haha

    Is there a way of getting it to compare side by side :)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would have to use a basic IF statement to compare the two row by row then count how many rows are flagged.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    But now that I think about it, there was a post some time back where a rather complex formula using nested COLLECT statements and a bunch of @cell references was used to do this. If I am not mistaken, I believe it was @L@123 that provided this.

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    There you go @s_mawer a lovely message thread for you :) Hope you find your answer

  • bentlb3
    bentlb3 ✭✭✭✭

    I believe my situation is similar to the one in this string, but here is my challenge:

    I need to count (countifs) the number of entries that have:

    • - a date in column 1
    • - and the selection in Column 2 is either a no, NA or blank (in other words anything other than "yes"

    The formula I am trying (and not succeeding with) is: =COUNTIFS({Column 1 Range 1}, <>"", {Column 2 Range 2}, <>"Yes")

    What I believe I'm doing with this formula is looking for any cell in Column 1 that is not blank (i.e. any cell that has a date entry), and then if the corresponding cell on that line is anything but "Yes" selected.

    for my example, I should be returning 17 counts, but only getting 1.

    Thank you for the help!!

    Brad

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    edited 04/21/22

    Hello

    Try this:

    =COUNTIFS([Column1]:[Column1], ISDATE(@cell), [Column2]:[Column2], NOT(@cell = "Yes"))

    Where Column1 is the Date column and Column2 is the column that you don't want a Yes in.

    Hope this works

    Kind regards

    Debbie

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @bentlb3 I would start with double checking the ranges are selected appropriately. Also apply a filter to the source sheet that mimics the formula exactly to see how many rows the filter pulls.

  • bentlb3
    bentlb3 ✭✭✭✭

    Thank you@Debbie Sawyer and @Paul Newcome! Your formula worked perfectly, Debbie!

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    That is great! Happy to have helped.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!