need to sum with offset
 

is there a way to sum items in a column when the column is variable? I am
thinking of using offset or match for the column reference, but am not sure
how to sum once I have the proper column. For example:

jan feb mar
apples 151,773.50 154,178.17 160,318.00
lemons 199,483.34 195,221.18 198,948.17
oranges 244,926.24 227,642.54 232,229.79
bananas 70,326.08 37,356.37 41,320.48
limes 263,423.60 243,861.72 230,643.48

I would like to compare citrus sales for January and March. So I'd like to
sum rows 3:4,6. But how can I use a formula to select column D? Also, next
month "Mar" may not be in column D.

I could add a column and add the grouping labels and then sumif, but that
would mean redesigning a massive workbook so I'm hoping there is another way.

Thanks for your thoughts and help :)

need to sum with offset
 

Assumptions:

A2:A6 contains the fruit

B1:D1 contains the month

B2:D6 contains the data

Formula:

To sum Jan, where the corresponding values in A2:A6 equals lemons,
limes, or oranges, try...

=SUMPRODUCT(--ISNUMBER(MATCH($A$2:$A$6,{"Lemons","Limes","Orange s"},0)),I
NDEX($B$2:$D$6,0,MATCH("Jan",$B$1:$D$1,0)))

Adjust the ranges, accordingly.

Hope this helps!

http://www.xl-central.com

In article ,
Fellow Wanderer wrote:

is there a way to sum items in a column when the column is variable? I am
thinking of using offset or match for the column reference, but am not sure
how to sum once I have the proper column. For example:

jan feb mar
apples 151,773.50 154,178.17 160,318.00
lemons 199,483.34 195,221.18 198,948.17
oranges 244,926.24 227,642.54 232,229.79
bananas 70,326.08 37,356.37 41,320.48
limes 263,423.60 243,861.72 230,643.48

I would like to compare citrus sales for January and March. So I'd like to
sum rows 3:4,6. But how can I use a formula to select column D? Also, next
month "Mar" may not be in column D.

I could add a column and add the grouping labels and then sumif, but that
would mean redesigning a massive workbook so I'm hoping there is another way.

Thanks for your thoughts and help :)