Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif w/ relative column reference
I would like to use sumif, but don't have a set column for the sum range.
For example: A B C D E 1 Apr Mar Feb Jan 2 3 apples 12 14 15 14 4 bananas 26 32 30 35 5 apples 7 7 4 5 6 grapes 19 22 22 20 7 oranges 6 5 6 7 I can use sumif(a:a,"apples",d:d) to sum February, but next month when I add May data February shifts out to column E. How do I create a reference to the proper column? I've been working with offset, indirect, match, etc but can't seem to come up with the right combination to make it work. Thanks for all of your thoughts! :) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif w/ relative column reference
Fellow Wanderer wrote:
I would like to use sumif, but don't have a set column for the sum range. For example: A B C D E 1 Apr Mar Feb Jan 2 3 apples 12 14 15 14 4 bananas 26 32 30 35 5 apples 7 7 4 5 6 grapes 19 22 22 20 7 oranges 6 5 6 7 I can use sumif(a:a,"apples",d:d) to sum February, but next month when I add May data February shifts out to column E. How do I create a reference to the proper column? I've been working with offset, indirect, match, etc but can't seem to come up with the right combination to make it work. Thanks for all of your thoughts! :) =SUMIF(A:A,"apples",OFFSET(A1,0,MATCH("Feb",B1:E1) ,99,1)) ~~~~~ ~~ Note 1: make B1:E1 conveniently wide (as many month columns you need) Note 2: make 99 conveniently tall (as many rows you need) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif w/ relative column reference
I went as far as column P with my table
The formula =SUMPRODUCT(--(A3:A7="apples")*B3:P7*(COLUMN(B3:P7)=COUNTA(B1:P1 )+1)) sums the data in the column with the last month in row 1 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Fellow Wanderer" wrote in message ... I would like to use sumif, but don't have a set column for the sum range. For example: A B C D E 1 Apr Mar Feb Jan 2 3 apples 12 14 15 14 4 bananas 26 32 30 35 5 apples 7 7 4 5 6 grapes 19 22 22 20 7 oranges 6 5 6 7 I can use sumif(a:a,"apples",d:d) to sum February, but next month when I add May data February shifts out to column E. How do I create a reference to the proper column? I've been working with offset, indirect, match, etc but can't seem to come up with the right combination to make it work. Thanks for all of your thoughts! :) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif w/ relative column reference
I assume you're inserting a new column B with the new months data.
Try this: A1 = the month that you want to calculate. For example, Mar. A2 = the item to sum. For example, apples =SUMIF(A3:A7,A2,INDEX(B3:E7,,MATCH(A1,B1:E1,0))) When you insert a new column B the ranges will automatically adjust. -- Biff Microsoft Excel MVP "Fellow Wanderer" wrote in message ... I would like to use sumif, but don't have a set column for the sum range. For example: A B C D E 1 Apr Mar Feb Jan 2 3 apples 12 14 15 14 4 bananas 26 32 30 35 5 apples 7 7 4 5 6 grapes 19 22 22 20 7 oranges 6 5 6 7 I can use sumif(a:a,"apples",d:d) to sum February, but next month when I add May data February shifts out to column E. How do I create a reference to the proper column? I've been working with offset, indirect, match, etc but can't seem to come up with the right combination to make it work. Thanks for all of your thoughts! :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CAN A HYPERLINK HAVE AN ABSOLUTE COLUMN & RELATIVE CELL REFERENCE. | Excel Worksheet Functions | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
Maintain Relative Reference After Inserting a Column | Excel Worksheet Functions | |||
Relative reference | Excel Worksheet Functions | |||
Relative Reference | Excel Discussion (Misc queries) |