Countif a cell is blank or dated before a date

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)<[email protected])) but it's coming up as UNPARSEABLE.

Can anyone help?

Thanks

Sarah

Best Answers

  • s_mawers_mawer
    Accepted Answer

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

Answers

  • 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

  • @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<[email protected]))

    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 [email protected] 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 < [email protected]))

    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 [email protected]

    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 NewcomePaul Newcome ✭✭✭✭✭

    @Debbie Sawyer

    It's the @row reference. [email protected]


    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.

  • @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 NewcomePaul 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 NewcomePaul 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 @[email protected] that provided this.

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

Sign In or Register to comment.