Sheet Summary formula

Hi,

I am using the below query to fetch a value from Finance column but this works if my criteria of Req Type = P but fails for Req Type = L. I believe this is something to do with INDEX Function but not sure how my requirement can be achieved. Can someone help me on fine tuning this query please!

Query works for Req Type = "P"

=INDEX(Finance:Finance, MAX(COLLECT([Row ID]:[Row ID],[Req Type]:[Req Type],"P",Quarter:Quarter,2,Year:Year,2021)),1)

Query fails for Req Type = "L"

=INDEX(Finance:Finance,MAX(COLLECT([Row ID]:[Row ID],[Req Type]:[Req Type],"L",Quarter:Quarter,2,Year:Year,2021)),1)

I welcome any solution either by modifying the query (as i am not sure if this is the right way to use) or entire new query to achieve my requirement.

my Smartsheet table data

Thanks in advance,

~Chitta

Find more posts tagged with

Accepted answers

All comments

David Tutwiler

I set up a replica of your sheet and copy/pasted your formulas into the cells called Values so that I could test it. Your formulas looks correct to me and I wanted to see if there were comma issues or a simple parentheses missing.

However, when I pasted the formula in with the data you provided it works great. Can you provide the error message you're seeing?

Screenshot attached of the sheet working with the formula you provided for the L lookup.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.