# Sum of a column defined by the text of another column, without duplicates

#### Estimator_J

##### New Member
Hello all,

I have one column in which state abbreviations are entered.

The second column has dollar amounts entered.

I want to add the sum of the dollar amount column, determined by the text of the state abbreviation column. i.e. total dollar value for each state.

Further, I only want unique dollar values summed. If the exact same (duplicate) value exists, I want it omitted from the sum.

Is this possible? Any assistance would be greatly appreciated!

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Snakehips

##### Well-known Member
Estimator_J,

Maybe like the below Array formula.
Excel Workbook
ABCDE
1State\$*State\$
2A13*A49
3E1***
4A5***
5A1***
6A22***
7C1***
8A13***
9A1***
10D5***
11F1***
12A1***
13A6***
14A2***
Sheet1

Hope that helps.

#### Estimator_J

##### New Member
Estimator_J,

Maybe like the below Array formula.
Sheet1

 * A B C D E 1 State \$ * State \$ 2 A 13 * A 49 3 E 1 * * * 4 A 5 * * * 5 A 1 * * * 6 A 22 * * * 7 C 1 * * * 8 A 13 * * * 9 A 1 * * * 10 D 5 * * * 11 F 1 * * * 12 A 1 * * * 13 A 6 * * * 14 A 2 * * *

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:87px;"><col style="width:80px;"><col style="width:82px;"><col style="width:75px;"><col style="width:75px;"></colgroup><tbody>
</tbody>

 Cell Formula E2 {=SUMPRODUCT((\$A\$2:\$A\$30=\$D\$2)*\$B\$2:\$B\$30*IFERROR(MATCH(\$A\$2:\$A\$30&\$B\$2:\$B\$30,\$A\$1:\$A\$30&B1:\$B\$30,0)=ROW(\$A\$2:\$A\$30),0))}

<tbody>
</tbody>
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Hope that helps.

It worked perfect! Thank you!

Replies
5
Views
131
Replies
1
Views
302
Replies
5
Views
725
Replies
1
Views
705
Replies
6
Views
529

1,191,118
Messages
5,984,749
Members
439,907
Latest member
Kayfabe

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back