Summary formula

Options

Hey I'm trying to do this in a Sheet Summary:

  • If [Date] is today, then show me the value in the same row in column G.

This works for an individual row (1)

=IF([Date]1 = TODAY(), [G]1, 0)

But I can't get it to work with @row to search all the way down the sheet to look for the current date, I've tried these:

=IF([Date]@row = TODAY(), [G]@row, 0) (unparseable)

=IF([Date]@cell = TODAY(), [G]@cell, 0) (unparseable)

=IF([Date]:[Date] = TODAY(), [G]:[G], 0) (invalid operation)

Also I saw some posts that TODAY doesn't work unless you open/edit the sheet. Is this correct and is it true when used in the Summary fields?

Answers

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    edited 07/10/24
    Options

    Hi

    I would recommend an Index(Match()) type formula.

    =INDEX(G:G, MATCH(TODAY(), Date:Date, 0))

    Here is a picture of an example working - today is 10th July.

    If there's more than 1 row with todays date in it, it would return the first match only. There are other functions that could COLLECT all the correct G values and JOIN them together in a list (I have capitalised Join and Collect as they are the function names that you would need to use).

    I hope this helps.

    Kind regards

    Debbie

    Debbie Sawyer

    CSSO (Chief Smartsheet Solutions Officer)

    Smarter Business Processes

    Smartsheet Community Champion & Ambassador

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    Options

    For the refreshing issues, I usually have a checkbox column hidden on the sheet called something like "Daily"

    You can have a workflow that checks the column on a daily basis and if Daily is set to 1 then change the cell to 0 and if it is set to 0 then change it to 1.

    This means that every day the system updates all the rows on your sheet to have either a checked box or an empty box and it forces the refresh on the today function.

    You can leave this hidden on the sheet, it runs while you are off line and the sheet remains in sync with todays date.

    Kind regards

    Debbie

  • rebeccakbaker
    Options

    Thank you! The INDEX function worked perfectly.

    I don't have the workflow plugin so I guess will have to edit the sheet

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    Options

    Ahh - are you a free collaborator? The admin on the sheet might be able to write you a workflow, or yes, just make sure you open the sheet daily! That will do it too!

  • rebeccakbaker
    Options

    Hi, no I am the admin on the sheet. I just thought I needed additional licenses to create workflows.

    But it looks like I can; time to dig in there I guess!

    Thanks again

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!