SUMIF on column defined in formula

[Aeroed7]

Hello,

I have a sheet with column headings as the month (Jan, Feb, Mar, etc) and data for each month in the rows beneath. See below:
Type 1/1/2011 2/1/2011 3/1/2011

a 5.6 5.3 5
b 2.1 4.2 6.3
c 3.5 8.1 12.7
a 6.1 2.3 -1.5
b 5.5 4.1 2.7
c 5.4 6.1 6.8
a 5.3 9.1 12.9
b 5.2 10.4 15.6
c 5.1 7.3 9.5

I want to write a SUMIF formula in one cell that will add up all of the "a" in a certain month.
I want to be able to change the month it is summing without going into the formula and changing the column.

Any thoughts?

Thank you

 

[buxtongt]

What about summing each row in the next column to the right? You could then have a cell with =subtotal(9,e:e) (if d is the column with the sums mentioned above), and you could filter the data by column a to see the criteria there you wanted to see (a,b,c, etc). Hope this helps.

 

[sheetspread]

If your range is A1:D10 (including headers) then ctrl-shift-enter in F2:

=SUM(IF($A$2:$A$10="a",IF(COLUMN($B$2:$D$10)=MATCH(F1,MONTH($B$1:$D$1),0)+1,$B$2:$D$10)))

and put a 1,2, or 3 in F1

 

[buxtongt]

You could also do a series of sumif functions, along the lines of:

=SUMIF(A:A,"=a",B:B)+SUMIF(A:A,"=a",C:C)

where a is the column you want to pull from and b and c are the number columns you want to add together. You could also point the = part to another cell instead of the value, so you can edit a cell and see different values, such as:

=SUMIF(A:A,H1,B:B)+SUMIF(A:A,H1,C:C)

if H1 is the cell with the value you want.