Summary formula

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?

Find more posts tagged with

Formulas

Sheet Summary

IF(

Accepted answers

All comments

Debbie Sawyer

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

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

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

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

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.