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:

A 16 A X C 7 B X B 3 C Z <tbody> </tbody><colgroup><col span="6"></colgroup> 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?
<tbody> </tbody><colgroup><col span="2"></colgroup>

<tbody>
</tbody><colgroup><col span="2"></colgroup>

 

[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]

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	A	16​			A	X		x	z
2​	C	7​			B	X		19​	7​
3​	B	3​			C	Z

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!!