PDA

View Full Version : [SOLVED:] return the value from 'A' which has the largest sum of values in 'B'



omp001
02-06-2017, 07:58 AM
Hi all.
Please, what formula would return "may" from the table below as it is the month which has the largest sum (120) of it's corresponding values in column 'B' ?
thanks in advance.

A.............B
jan..........10
mar.........20
oct..........30
may.........40
jan..........50
mar.........60
oct..........70
may.........80
set..........90
nov........100

SamT
02-06-2017, 12:15 PM
Please, what formula would return "nov" from the table below as it is the month which has the largest sum (100) of it's corresponding values in column 'B' ?
thanks in advance.

omp001
02-06-2017, 12:41 PM
Thanks for reply.
"may" is the month with largest sum:
A4 = may and B4 = 40
A8 = may and B8 = 80
so the sum corresponding to "may" is 120

JBeaucaire
02-06-2017, 09:32 PM
This is array formula that will accomplish this based on using the known abbreviations of months as an array:

=INDEX({"jan";"feb";"mar";"apr";"may";"jun";"jul";"aug";"set";"oct";"nov";"dec"}, MATCH(TRUE, SUMIF(A2:A11,{"jan";"feb";"mar";"apr";"may";"jun";"jul";"aug";"set";"oct";"nov";"dec"},B2:B11)=MAX(SUMIF(A2:A11,{"jan";"feb";"mar";"apr";"may";"jun";"jul";"aug";"set";"oct";"nov";"dec"},B2:B11)),0))

....confirmed by pressing CTRL+SHIFT+ENTER to activate the array. You will know the array is active when curly braces {} appear around your formula.

JBeaucaire
02-06-2017, 09:35 PM
You can also open the Name Manager (CTRL+F3) and create a new named formula called Months with this formula:
={"jan";"feb";"mar";"apr";"may";"jun";"jul";"aug";"set";"oct";"nov";"dec"}

Then the formula above could be shortened to this, easier to read:
=INDEX(Months, MATCH(TRUE, SUMIF(A2:A11,Months,B2:B11)=MAX(SUMIF(A2:A11,Months,B2:B11)),0))

....still an array.

omp001
02-08-2017, 10:22 AM
Sorry for delay.
I went with the named range version. That did the job nicely.
Thanks a lot.

JBeaucaire
02-08-2017, 10:37 AM
Glad to help, please use the THREAD TOOLS link above to mark this thread as [SOLVED]. Thanks.

p45cal
02-09-2017, 06:15 PM
…and with a simple pivot table. See attached.