Sum of a column (w/out duplicates) based on 3 conditions

[Estimator_J]

Hello,

I have one column where the "State" is listed.
Another column states "Public" or "Private".
I would like to sum the "Amount" column for each state based on public or private.
However, there are duplicates in the amount column so I need the result to not include duplicates.

I have another formula for figuring out the amount based on state only but I would like to take it a step further and add another condition, public or private. Does anyone have an idea of how to structure the formula for the third condition?

Thank you!

 

[jorismoerings]

Hi,

Take a look at this and see if this will help:

Book1
	A	B	C	D	E
1	state	status	amount		Florida
2	Florida	Public	100		Public
3	Florida	Private	200		201
4	Texas	Public	100
5	Texas	Private	200
6	Florida	Public	101
7	Florida	Private	200
8	Texas	Public	102
9	Texas	Private	200
Sheet1

Cell Formulas
Range	Formula
E3	{=SUM(IF(FREQUENCY(IF(A2:A9=E1,IF($B$2:$B$9=E2,IF($C$2:$C$9<>"",MATCH($C$2:$C$9,$C$2:$C$9,0)))),ROW($B$2:$B$9)-ROW($B$2)+1),$C$2:$C$9))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Estimator_J]

It worked beautifully! Thank you!

Hi,

Take a look at this and see if this will help:

	A	B	C	D	E
1	state	status	amount		Florida
2	Florida	Public	100		Public
3	Florida	Private	200		201
4	Texas	Public	100
5	Texas	Private	200
6	Florida	Public	101
7	Florida	Private	200
8	Texas	Public	102
9	Texas	Private	200

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

Sheet1​

Array Formulas Cell Formula E3 {=SUM(IF(FREQUENCY(IF(A2:A9=E1,IF($B$2:$B$9=E2,IF($C$2:$C$9<>"",MATCH($C$2:$C$9,$C$2:$C$9,0)))),ROW($B$2:$B$9)-ROW($B$2)+1),$C$2:$C$9))} <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>