# Sumif Help - with unique column

#### Mr Retirement

##### New Member
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.

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### Eric W

##### MrExcel MVP
Consider:

ABCDEFG
1Formula ColumnNameIDAmountHelperFormula
2550ABC123200200550
3550ABC1232000550
4550ABC234350350550
5450BCD345300300450
6450BCD456150150450

</tbody>
Sheet1

Worksheet Formulas
CellFormula
E2=(COUNTIFS(\$B\$2:\$B2,B2,\$C\$2:\$C2,C2)=1)*D2
F2=SUMIF(B:B,B2,E:E)

</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
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)))}

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

##### Well-known Member

Excel Workbook
ABCD
1Formula ColumnNameIDAmount
2550ABC123200
3550ABC123200
4550ABC234350
5450BCD345300
6450BCD456150
Sheet

Replies
2
Views
341
Replies
17
Views
476
Replies
4
Views
323
Replies
1
Views
330
Replies
2
Views
264

1,108,924
Messages
5,525,656
Members
409,658
Latest member
Yardcell