IF, AND statement with 2 criteria to return text from reference sheet

Need some help..

I am referencing another sheet for all of my references

I need the formula to return the name of the project if the deadline date is within 210 days from today's date and the date paid does not have a date.

=IF(AND({Deadline Range 1} >= TODAY(210), {Date Paid Range 2} <> ""), {Project Range 3}, 0)


  • Kelly Drake
    Kelly Drake Overachievers Alumni

    Try this:

    = IF(AND({Deadline Range 1} <= TODAY()+210, ISBLANK({Date Paid Range 2}), {Project Range 3}, 0)

    Kelly Drake (she/her/hers)

    STARBUCKS COFFEE COMPANY| senior systems analyst | global growth & concepts


    =IF(AND({Deadline Range 1} <= TODAY() + 210, ISBLANK({Date Paid Range 2}), {Project Range 3}, 0))

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @kim b

    In this instance, I would recommend using a JOIN(COLLECT formula. This way if there's more than one Project that fits your criteria you'll see the names of all of them, versus just the first match.

    Try this:

    =JOIN(COLLECT({Project Range 3}, {Deadline Range 1}, @cell <= TODAY(210), {Date Paid Range 2}, ""), " / ")

    If there are many projects that fit this criteria, you may want to think about creating a Report instead, so you can view more details about each Project. Let me know if you need help building this out!



  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 05/25/21

    Hi @kim b 

    Hope you are fine, please try the following formula:

    =JOIN(COLLECT({Project Range 3}, {Date Paid Range 2}, ISBLANK(@cell), {Deadline Range 1}, TODAY(-210) <= @cell), " - ")

    or @Genevieve P formula ( she posted it before me but i didn't notice until i posted mine )

    PMP Certified

    [email protected]


    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Hi, thanks for getting back to me.

    Genevieve's returned an #INCORRECT ARGUMENT and the one you provided gave me a return of the project name, but it also included all of the other project names in the same cell.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @kim b

    Yes, the JOIN function will join together all Projects that meet your criteria. Do you have a different criteria to indicate which project name you want to bring back? I would presume there are multiple that have a deadline within 210 days. Would a Report be better?

  • Hi Genevieve,

    I tried a report, but it will not allow me to use it as the data source for a metric widget, which is my ultimate goal.

    Maybe I'm not explaining myself clearly. I am looking for a way to pull a project name (column A) into another sheet if the deadline for that specific project is within 210 days from today's date and the date paid column is blank.


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @kim b

    You're right, you would want to embed the Report as a Report Widget instead of a Metric Widget.

    When you note "a project name", how are you finding this one project?

    It sounds like when you used @Bassam Khalil's formula it pulled in a number of Projects into one cell, right? This is because there is more than one project that is within 210 days from Today's Date with a Date Paid cell showing as blank.

    What other criteria can help filter this down to only show the one project you're looking for? Or do you want to see all of the project names that the formula pulled in, but separately (versus in the same cell)?

  • thanks for the quick response..

    I want the project names to be listed separately with their corresponding deadline date next to it.

    So if the project column A meets the criteria I want that name to be pulled into another sheet and in the column next to it, the deadline date. My goal is to have all Projects with upcoming deadlines shown on the dashboard.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @kim b

    Thanks for clarifying! There currently isn't a way to do this with a formula where it will parse out the project names down rows in the way you're describing. The way to do this would be to create a Report and use a Report Widget (see here) on the Dashboard instead.

    The formula suggested above is the other alternative, but as you saw, this will JOIN together all the Project Names into one cell if there's more than one.

    A Report Widget will identify all Projects with upcoming deadlines, and easily display any other associated information (such as the Date column you mentioned). This can then be shown in the Dashboard. You mentioned trying a Report before, have you tested with the Report Widget instead of a Metric Widget?

  • Well, that's unfortunate, but I appreciate you taking the time in answering my questions.

    Yes, I have a report with this data and will do it that way.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!