IF AND CONTAINS with >=TODAY(-7)

Options
A Rose
A Rose ✭✭✭✭
edited 09/14/23 in Formulas and Functions

Hi,

I have a formula I need help with please,

=IF(AND(CONTAINS([Client Name]@row, {Sheet A Range 1}), IF({Sheet A Range 2}, >=TODAY(-7))), "Match")

______________

Name = Text column

Sheet A Range 1 = Text column

Sheet A Range 2 = Date column

______________

Any idea how to correct this #INVALID DATA TYPE?

Thank you!

Best Answers

  • A Rose
    A Rose ✭✭✭✭
    Answer ✓
    Options

    "Paul Newcome

    Paul Newcome ✭✭✭✭✭✭

    4:59PM

    Give this a try:

    =IF(COUNTIFS({Sheet A Range 1}, CONTAINS([Client Name]@row, @cell), {Sheet A Range 2}, @cell>= TODAY(-7))> 0, "Match")


    10xViz.com


    Come see me at ENGAGE 2023! If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth. I will also be doing a short demonstration in the Experience Hub Theater on Tuesday! "

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hey @A Rose

    In your second COUNTIFS you'll just need to remove the >1 bit of it.

    =IF(COUNTIFS([Client Name]:[Client Name], [Client Name]@row) > 1, COUNTIFS([Client Name]:[Client Name], [Client Name]@row))

    What would you like it to do if the count is just 1? Currently this will return a blank cell.

    Cheers,

    Genevieve

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!