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

[Estimator_J]

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!

 

[Snakehips]

Estimator_J,

Maybe like the below Array formula.

Excel Workbook
	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	*	*	*
Sheet1

Adjust to suit your ranges.

Hope that helps.

 

[Estimator_J]

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>

Spreadsheet Formulas

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

Adjust to suit your ranges.

Hope that helps.

It worked perfect! Thank you!