Hi Excel Forum Help,
With the following data, I am trying to answer "who sold the most total units, and who had the most total revenue?". I have the formula, but would appreciate your help understanding why the formula works.
The solutions are:
Most total units {=INDEX(D:D,MATCH(MAX(SUMIF(D:D,D:D,B:B)),SUMIF(D:D,D:D,B:B),0))}
Most total revenue {=INDEX(D:D,MATCH(MAX(SUMIF(D:D,D:D,C:C)),SUMIF(D:D,D:D,C:C),0))}
Columns A = Date
Column B = Units
Column C = Revenue
Column D = Name of rep
My questions specifically are:
1 Specifically, what does it mean when you use (MAX(SUMIF(D:D,D:D,B:B)),SUMIF(D:D,D:D,B:B),0) as the arguments for MATCH?
2 What does it mean when the criteria for the sumif is also its range in the context of an array?
3 I understand the structure of Index + Match, but don't understand why the SUMIFs work as the arguments for Match.
4 Why does this only work as an array function?
Thank you so much! Appreciate your enlightenment.
Bookmarks