Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 :) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with Offset | Excel Discussion (Misc queries) | |||
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul | Excel Worksheet Functions | |||
Offset Help | Excel Worksheet Functions | |||
Offset. | Excel Worksheet Functions | |||
SUM(OFFSET)? | Excel Worksheet Functions |