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

Shawn Church

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:

Paul Newcome

You would need an INDEX/COLLECT instead:

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

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. 👍️