Countif a cell is blank or dated before a date

s_mawer

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

s_mawer

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

Paul Newcome

@s_mawer It looks like you've got it. Well done!

Don't forget to flag your response as "helpful" so that it is marked as the "Best Answer". This will let other people searching for a similar solution know that one may be found here.

s_mawer

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

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

Paul Newcome

@s_mawer It looks like you've got it. Well done!

Don't forget to flag your response as "helpful" so that it is marked as the "Best Answer". This will let other people searching for a similar solution know that one may be found here.

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

@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

@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

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

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

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

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

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

@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

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

Debbie Sawyer

That is great! Happy to have helped.