Sumif Help - with unique column

Mr Retirement

New Member
Joined
Nov 12, 2016
Messages
42
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 ColumnNameIDAmount
? (should equal 550)ABC123200
? (should equal 550)ABC123200
? (should equal 550)ABC234350
? (should equal 450)BCD345300
? (should equal 450)BCD456150

<tbody>
</tbody>


Thanks,
Mr R.
 

Some videos you may like

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
Joined
Aug 18, 2015
Messages
10,219
Consider:

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

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

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

<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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
How about

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

Watch MrExcel Video

Forum statistics

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

This Week's Hot Topics

Top