Sum with changing range

[evanc123]

Dear All,

I am wondering if it's possible to generate a formula that will give me the following desired result:

[TABLE="width: 128"]
<tbody>[TR]
[TD][TABLE="width: 384"]
<tbody>[TR]
[TD]A[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[TD]Z[/TD]
[/TR]
</tbody><colgroup><col span="6"></colgroup>[/TABLE]


Is it possible to get the answer 23 (16+7) with some kind of SUMIF formula for "X", then maybe an offset to collect results "A" and "B" which are adjacent to "X", then find the total for A+B in another column? [/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 128"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col span="2"></colgroup>[/TABLE]
[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody><colgroup><col span="2"></colgroup>[/TABLE]

 

[evanc123]

Edit: - the answer would be 19 (16+3) where A=16 and B=3 - sorry!

 

[Scott Huish]

Assuming that is columns A:D

Your first formula: =SUM(SUMIF(A:A,{"A","B"},B:B))
Your second formula: =SUM(SUMIFS(B:B,C:C,{"A","B"},D:D,"X"))

Excel 2010
	A	B	C	D	E	F	G
1	A	16	A	X		19	23
2	C	7	B	X
3	B	3	C	Z
4
Sheet1

Cell Formulas
Range	Formula
F1	=SUM(SUMIF(A:A,{"A","B"},B:B))
G1	=SUM(SUMIFS(B:B,C:C,{"A","B"},D:D,"X"))

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][/tr][tr][td]

1​

[/td][td]A[/td][td]

16​

[/td][td][/td][td][/td][td]A[/td][td]X[/td][td][/td][td]x[/td][td]z[/td][/tr]
[tr][td]

2​

[/td][td]C[/td][td]

7​

[/td][td][/td][td][/td][td]B[/td][td]X[/td][td][/td][td]

19​

[/td][td]

7​

[/td][/tr]
[tr][td]

3​

[/td][td]B[/td][td]

3​

[/td][td][/td][td][/td][td]C[/td][td]Z[/td][td][/td][td][/td][td][/td][/tr]
[/table]

In H2 control+shift+enter, not just enter, and copy across to G2...

=SUM(SUMIFS($B$1:$B$3,$A$1:$A$3,IF($F$1:$F$3=H1,$E$1:$E$3)))

 

[evanc123]

AWESOME insight - many thanks to you both!!