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! :)

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)

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! :)

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! :)