formula for =columns subtotals find the max value

[duplik8]

hi i'm trying to figure out the formula in excel 2010 to do the following:
let's say i have 5 columns and i wanted to get the total for each columns and then get the max value and return the column name
is that possible?
please helpcolumn subtotals find max.xls

[benishiryo]

hi duplik8, welcome to the forum. try this array formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[duplik8]

OMG! i have been googling all possible terms and keywords to get an answer to this and the solution is just here.
thank you so much benishiryo for your help.

at first i got an error # NA and did what you said to press F2 and try again it did work.

do you mind explaining to me what you just did in the formula please if you don't mind... sorry i'm not really a good excel user.

thank you heaps again.

[benishiryo]

to explain the SUBTOTAL & OFFSET portion:

what we want to achieve is to compare 5 individual summation of B2:B4, C2:C4, all the way to F2:F4. to achieve this in a single cell, you can use the OFFSET formula. OFFSET has 5 arguments; reference, rows, cols, [height],[width]

reference
this is the anchor cell on where you want to start. in the eg. let's start in B2

rows
from the anchor cell, this determines how many rows away you want to move from. 0 would mean that it stays in B2, 1 would move to B3. -1 would move to B1. by not putting any values, it's equivalent to putting 0. i did not input anything because I want to start from B2.

cols
from the anchor cell, this determines how many columns away you want to move from. 0 would mean that it stays in B2, 1 would move to C2. -1 would move to A2. what i did was to input the formula this formula:
COLUMN(B2:F2)
if you select this portion & press F9 to calculate, you'll see that it returns the column number from B2:F2 (where column A would return 1, column B to return 2, & so on):
{2,3,4,5,6}
what i need is for it to have 5 different starting points; from B2 (0 columns away), C2 (1 column away), D2 (2 columns away), & so on. so i just have to deduct the column number of my starting column:
COLUMN(B2:F2)-COLUMN(B2)
that will return:
{0,1,2,3,4}

[height]
when you see square brackets surrounding the argument, it is an optional field with a default value. the default value is 1, because the cell needs to be 1 cell high. 2 cells high would be B2:B3. we need 3 rows, so i used the ROWS formula. the ROWS formula counts the number of rows you have in the input range.

[width]
again, this is with square brackets. default value also 1 because cell needs to be 1 cell wide. 2 cells wide would mean to range B2:C2. i left it empty because we only need to sum 1 column for each individual range.

put all of that in the SUBTOTAL with function number 9, it will sum up the 5 ranges we want:
=SUBTOTAL(9,OFFSET(B2,,COLUMN(B2:F2)-COLUMN(B2),ROWS(B2:B4)))
again, you can select this portion & press F9 to calculate. you will get the 5 individual summation of:
{14,18,22,26,14}
where 14 is sum of B2:B4, 18 is sum of C2:C4, & so on.

hope that helps. and please mark this as "Solved"