Sumif Help - with unique column

[Mr Retirement]

Hello!

I'm looking for a formula (maybe sumifs?) that will lookup the 'Name' column and 'ID' column and return the sum of the 'Amount' column BUT for only those ID's that are unique. For example, if I lookup Name "ABC" it should return 550 because it'll sum 200 + 350... not both 200's because they have the same ID. I have the ability to create helper columns if needed. Any suggestion is great!

Formula Column	Name	ID	Amount
? (should equal 550)	ABC	123	200
? (should equal 550)	ABC	123	200
? (should equal 550)	ABC	234	350
? (should equal 450)	BCD	345	300
? (should equal 450)	BCD	456	150

<tbody>
</tbody>

Thanks,
Mr R.

 

[Eric W]

Consider:

	A	B	C	D	E	F	G
1	Formula Column	Name	ID	Amount	Helper	Formula
2	550	ABC	123	200	200	550
3	550	ABC	123	200	0	550
4	550	ABC	234	350	350	550
5	450	BCD	345	300	300	450
6	450	BCD	456	150	150	450

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E2 =(COUNTIFS($B$2:$B2,B2,$C$2:$C2,C2)=1)*D2 F2 =SUMIF(B:B,B2,E:E) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula A2 {=SUM(IF($B$2:$B$6=B2,IF(MATCH(B2&"|"&$C$2:$C$6,$B$2:$B$6&"|"&$C$2:$C$6,0)=ROW($C$2:$C$6)-ROW($C$2)+1,$D$2:$D$6)))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

The array formula in A2 doesn't need a helper column, but may be slower than the helper column/formula version in E:F. Hope one of them works for you.

 

[DanteAmor]

How about

Excel Workbook
	A	B	C	D
1	Formula Column	Name	ID	Amount
2	550	ABC	123	200
3	550	ABC	123	200
4	550	ABC	234	350
5	450	BCD	345	300
6	450	BCD	456	150
Sheet