COUNTIFS with OR included

Jenna Matthews

Good Afternoon,

I've got a doozy of a formula that I just can't figure out. I've been on the phone with smartsheet and it is still not counting correctly.

Here is what I need it to count:

-Range 1=on or before 12/1/20

-Range 2: Consultant name listed in the row

-Range 3: Blank OR (on or after 1/1/21)

Here is the formula I wrote:

=COUNTIFS({Range 1}, <=DATE(2020, 12, 1), {Range 2}, Consultant@row) + COUNTIFS({Range 2}, Consultant@row, {Range 3}, ="", {Range 3}, >=DATE(2021, 1, 1))

The Problem:

I think it's the last part of the formula that isn't working. It is still counting when Range 3 contains a date of 12/1/20. I need it to only count if it is ON or AFTER 1/1/21

THANK YOU FOR YOUR HELP!

Mike Wilday

Try this rendition. You may have to recreate your ranges. I recommend naming those cross sheet ranges for clarity. Each cross sheet range should only be one column.

=COUNTIFS({Range 1}, <=DATE(2020, 12, 1), {Range 2}, Consultant@row, {Range 3}, OR(ISBLANK(@cell), @cell >= Date (2021, 1,1)))

Mike Wilday

Try this rendition. You may have to recreate your ranges. I recommend naming those cross sheet ranges for clarity. Each cross sheet range should only be one column.

=COUNTIFS({Range 1}, <=DATE(2020, 12, 1), {Range 2}, Consultant@row, {Range 3}, OR(ISBLANK(@cell), @cell >= Date (2021, 1,1)))

Jenna Matthews

@Mike Wilday I think that worked!!! Thank you so much!