Sum If Is Not Summing Horizontally

[Guzzlr]

=SUMIF(B2:B4,B2,F2:J2)

Hello All, I ran into an issue using SUMIF. The formula above only pulls the number in F2. I want the SUMIF to sum horizontally, instead of vertically.
I thought it would sum horizontally?
thanks

 

[oldbrewer]

	2	60			10	11	12	13	14
	3				17	18	19	20	21
	2	130			24	25	26	27	28
		is this what you want as output ?

<colgroup><col width="64" span="12" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Guzzlr]

	2	60			10	11	12	13	14
	3				17	18	19	20	21
	2	130			24	25	26	27	28
		is this what you want as output ?

<tbody>
</tbody>

Not exactly, the criteria column would be something like 2,3,4. If the criteria was 2, it would be 60, if the criteria was 3 it would be 95.
There would be multiple criteria 2 down the single column of B:B, all adding Column F:N, based upon Criteria 2 every time the criteria 2 would be found in Column B:B.
Criteria 3 in Column B:B would add column F:N everywhere Criteria 3 was found in Column B:B
Thanks for the help

 

[Scott Huish]

SUMIF can sum horizontally, but your criteria range is vertical and your sum range is horizontal which is what is causing the problem.

Is this what you want?

=SUMPRODUCT((B2:B4=B2)*F2:J4)

Excel 2010
	B	C	D	E	F	G	H	I	J
2	2				10	11	12	13	14
3	3				17	18	19	20	21
4	2				24	25	26	27	28
5
6
7			190
Sheet1

Cell Formulas
Range	Formula
D7	=SUMPRODUCT((B2:B4=B2)*F2:J4)

 

[Guzzlr]

Type	12/27	1/3	1/10	1/17	1/24	1/31	2/7	2/14
BCWS	633.9	635.7	750.3	716.0	710.92
BCWP	49.9	569.5	1568.3	1307.5	898.43
ACWP	232.5	336.6	1502.0	1422.3	1000.80
BCWS	635.2	714.4	1874.0	1663.9	1245.52
BCWP	188.7	378.5	1686.0	1391.4	1615.24
ACWP	157.5	275.0	1314.3	1644.4	1488.80
BCWS	472.2	451.2	1301.9	1155.8	1157.61
BCWP	67.2	121.0	828.7	659.5	760.44
ACWP	78.9	98.7	794.1	725.5	843.70
Type	12/27	1/3	1/10	1/17	1/24	1/31	2/7	2/14
BCWS	1741	1801	3926	3536	3114	0	0	0
BCWP	306	1069	4083	3358	3274	0	0	0
ACWP	469	710	3610	3792	3333	0	0	0

<colgroup><col style="width:48pt" width="64" span="9"> </colgroup><tbody>
</tbody>

This is what I'm building so I can make a chart based upon BCWS, BCWP, ACWP numbers. so the totals sum each of the 3 criteria.
Thanks

 

[Fluff]

Are those the values that you want?
As from that layout you seem to adding the values vertically not horizontally.

 

[oldbrewer]

Type	Dec-27	01-Mar	01-Oct	Jan-17	Jan-24	Jan-31	02-Jul	Feb-14
BCWS	633.9	635.7	750.3	716	710.92
BCWP	49.9	569.5	1568.3	1307.5	898.43
ACWP	232.5	336.6	1502	1422.3	1000.8
BCWS	635.2	714.4	1874	1663.9	1245.52
BCWP	188.7	378.5	1686	1391.4	1615.24
ACWP	157.5	275	1314.3	1644.4	1488.8
BCWS	472.2	451.2	1301.9	1155.8	1157.61
BCWP	67.2	121	828.7	659.5	760.44
ACWP	78.9	98.7	794.1	725.5	843.7
Type	Dec-27	01-Mar	01-Oct	Jan-17	Jan-24	Jan-31	02-Jul	Feb-14
BCWS	1741	1801	3926	3536	3114	0	0	0
BCWP	306	1069	4083	3358	3274	0	0	0
ACWP	469	710	3610	3792	3333	0	0	0
BCWS	1741.3	1801.3	3926.2	3535.7	3114.05	0	0	0
BCWP	305.8	1069	4083	3358.4	3274.11	0	0	0
ACWP	468.9	710.3	3610.4	3792.2	3333.3	0	0	0
			my lower table uses this simple formula
			in B20 to the right of BCWS
			=SUMPRODUCT(($A$2:$A$10=$A20)*(B$2:B$10))
			dragged across and down

<colgroup><col width="64" span="12" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

Maybe:

=SUM(INDEX(F2:J4,MATCH(B2,B2:B4,0),0))

 

[Guzzlr]

Are those the values that you want?
As from that layout you seem to adding the values vertically not horizontally.

Correct, currently I'm having to add each criteria vertically, one column at at time. I was trying to add horizontally, and vertically. I havent tried the other suggestions yet...ill get to that later today I hope.
thanks for the help