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
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.