Is Sumif the right formula to use here?

[Tigerexcel]

I would like a conditional column total depending on which month is randomly selected:

Jan	Feb	Mar			Feb
1	5	1
8	5	2		Total for	5
9	4	4
7	5	3
1	1	6

In this case if cell F1 reads Feb, the total should be 20 but i get 5, my formula is only picking up the first cell. The formula I have is =SUMIF(A1:C1,F1,A2:C6). Can this Sumif formula be adjusted to work here, or is there a better option?

 

[shaowu459]

MrExcel.xlsx
	A	B	C	D	E	F
1	Jan	Feb	Mar			Feb
2	1	5	1
3	8	5	2		Total for	20
4	9	4	4
5	7	5	3
6	1	1	6
7
Sheet5

Cell Formulas
Range	Formula
F3	F3	=SUM(OFFSET(A2:A6,,MATCH(F1,A1:C1,)-1))

MrExcel.xlsx
	A	B	C	D	E	F
1	Jan	Feb	Mar			Mar
2	1	5	1
3	8	5	2		Total for	16
4	9	4	4
5	7	5	3
6	1	1	6
Sheet5

Cell Formulas
Range	Formula
F3	F3	=SUM(OFFSET(A2:A6,,MATCH(F1,A1:C1,)-1))

 

[Fluff]

How about
=SUMPRODUCT((A1:C1=F1)*(A2:C6))
or
=SUM(INDEX(A2:C6,,MATCH(F1,A1:C1,0)))

 

[shaowu459]

SUM function:

MrExcel.xlsx
	A	B	C	D	E	F
1	Jan	Feb	Mar			Mar
2	1	5	1
3	8	5	2		Total for	16
4	9	4	4
5	7	5	3
6	1	1	6
Sheet5

Cell Formulas
Range	Formula
F3	F3	=SUM(A2:C6*(A1:C1=F1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Tigerexcel]

Thanks Shaowu and Fluff. The last 3 formulae work, the offset formula calculates all months rather than the specific month required.

I can see now that SUMIF was the incorrect choice here. If I wanted to make the calculation more dynamic (as rows and extra columns will be added at a later time), which of these functions would be easiest to alter to allow for this?

 

[shaowu459]

Thanks Shaowu and Fluff. The last 3 formulae work, the offset formula calculates all months rather than the specific month required.

I can see now that SUMIF was the incorrect choice here. If I wanted to make the calculation more dynamic (as rows and extra columns will be added at a later time), which of these functions would be easiest to alter to allow for this?

The offset formula seems OK for me, have you copied the formula in my post?

 

[shaowu459]

Thanks Shaowu and Fluff. The last 3 formulae work, the offset formula calculates all months rather than the specific month required.

I can see now that SUMIF was the incorrect choice here. If I wanted to make the calculation more dynamic (as rows and extra columns will be added at a later time), which of these functions would be easiest to alter to allow for this?

All the above formulas will be easily modified to suit expanded ranges.

 

[shaowu459]

MrExcel.xlsx
	A	B	C	D	E	F
1	Jan	Feb	Mar			Feb
2	1	5	1
3	8	5	2		Total for	20
4	9	4	4
5	7	5	3
6	1	1	6
7
Sheet5

Cell Formulas
Range	Formula
F3	F3	=SUM(OFFSET(A2:A6,,MATCH(F1,A1:C1,)-1))

MrExcel.xlsx
	A	B	C	D	E	F
1	Jan	Feb	Mar			Mar
2	1	5	1
3	8	5	2		Total for	16
4	9	4	4
5	7	5	3
6	1	1	6
Sheet5

Cell Formulas
Range	Formula
F3	F3	=SUM(OFFSET(A2:A6,,MATCH(F1,A1:C1,)-1))

In this post i showed two cases, seems get the right result.

 

[Tigerexcel]

Apologies Shaowu, I used a named range in your offset formula and that picked up the other months. Your formula works fine.

All the above formulas will be easily modified to suit expanded ranges.

Just to clarify, I was after a dynamic update rather than manually altering the formula.

 

[Fluff]

Best way to make it dynamic is to turn the data into a structured table

+Fluff New.xlsm
	A	B	C	D	E	F	G
1	Jan	Feb	Mar	Apr			apr
2	1	5	1	1
3	8	5	2	2		Total for	28
4	9	4	4	3
5	7	5	3	4
6	1	1	6	5
7	2	3	4	6
8	5	6	7	7
9
Data

Cell Formulas
Range	Formula
G3	G3	=SUM(INDEX(Table1,,MATCH(G1,Table1[#Headers],0)))