Need a better Sheet Summary Formula - (Collect/Max & Index/Match)

In my example, I am able to return the top Sales for an item based on certain criteria for a Sheet Summary formula (Shirt Top Sales) with =MAX(COLLECT(Sales:Sales, Product:Product, "Shirt")). I am also able to return the Color for the Top selling Shirt (Blue) with a formula using the previous summary value in the formula: =INDEX(Color:Color, MATCH([Shirt Top Sales]#, Sales:Sales, 1)).

Is there a better formula to return the color of the top selling shirt? Suppose there are two matching values sales? That would potentially return the incorrect value.

In my example, if a Coat or Pants item had the same sales amount… the formula would return the color of that item instead of the Shirt.

Here is the incorrect Sheet Summary value based on another item with the same sales:

Find more posts tagged with

Sheets

Formulas

Sheet Summary

Accepted answers

Paul Newcome

You would need an INDEX/COLLECT instead:

=INDEX(COLLECT(Color:Color, Sales:Sales, @cell = [Shirt Top Sales]#, Product:Product, @cell = "Shirt"), 1)

All comments

Paul Newcome

You would need an INDEX/COLLECT instead:

=INDEX(COLLECT(Color:Color, Sales:Sales, @cell = [Shirt Top Sales]#, Product:Product, @cell = "Shirt"), 1)

Shawn Church

Of course! That makes total sense. Thanks for the answer.

Paul Newcome

Happy to help. 👍️

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.