Sumif w/ relative column reference

  • Thread starter Thread starter Fellow Wanderer
  • Start date Start date
F

Fellow Wanderer

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! :)
 
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
 
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
 
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.
 
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.
 
Back
Top