COUNTIFS with OR included

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!

Tags:

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!