Sheet Summary Formula to pull Month and FY based on Highest Value

I have a sheet with separate data fields to create stacked bar charts in my dashboard (one pictured below). I would like to highlight the month/year with the highest value on the dashboard with sheet summary metrics. I have a sheet summary field (Most Sales in a Month) to calculate the highest value using =MAX([FY2023]2:[FY2027]13).

Is there a way to have another sheet summary field that would pull the Month and FY? In the pictured example, it would be February FY 2026. So far I have tried these formulas to pull the FY or Month separately so I can concatenate the results but all result in errors:

=INDEX([FY2023]1:[FY2027]1, MATCH([Most Sales in a Month]#, [FY2023]2:[FY2027]13))

=INDEX([FY2023]2:[FY2027]13, MATCH([Most Sales in a Month]#, [FY2023]1:[FY2027]13, 0))

=INDEX([Month]2:[Month]13, MATCH([Most Sales in a Month]#, [FY2023]2:[FY2027]13, 0))

Find more posts tagged with

Formulas

index/match

Accepted answers

DKazatsky2

Hi @Mad Ed,

I have a solution that should work for you. Create a new Sheet Summary field called "Table position" and use the following formula.

=MATCH([Most Sales in a Month]#, [FY2023]2:[FY2027]13, 0)

Next, create another Sheet Summary field for the concatenated value you are trying to find and use the following formula.

=INDEX(Month:Month, ROUNDDOWN(MATCH([Most Sales in a Month]#, [FY2023]1:[FY2027]13, 0) / 5) + 1) + " " + IF(MOD([Table position]#, 5) = 0, INDEX([FY2023]1:[FY2027]1, 1, 5), INDEX([FY2023]1:[FY2027]1, 1, MOD([Table position]#, 5)))

Hope this helps,

Dave

All comments

DKazatsky2

Hi @Mad Ed,

I have a solution that should work for you. Create a new Sheet Summary field called "Table position" and use the following formula.

=MATCH([Most Sales in a Month]#, [FY2023]2:[FY2027]13, 0)

Next, create another Sheet Summary field for the concatenated value you are trying to find and use the following formula.

Hope this helps,

Dave

Melissa Yamada

I would set it up so the year is in a single column. That will simplify your look up. If you need the data in the way you have it for the stacked column, you can create helper columns in this format using the same data.

Mad Ed

@DKazatsky2 Thank you so much! That worked perfectly and I was able to replicate it for my other data grids in the sheet. Adding formula used for future users to reference (bolded the parts I updated).

=INDEX(Month:Month, ROUNDDOWN(MATCH([Most Grid 2 Sales in a Month]#, [FY2023]16:[FY2027]28, 0) / 5) + 1) + " " + IF(MOD([Grid 2 Table Position]#, 5) = 0, INDEX([FY2023]16:[FY2027]16, 1, 5), INDEX([FY2023]16:[FY2027]16, 1, MOD([Grid 2 Table Position]#, 5)))

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.