Sum values based on distinct values in another column plus another criteria

Question

I am trying to find the value of the Sale $ based on the unique Deal #s by location. I can get a total value for unique Deal #s using

=SUMPRODUCT(C11:C23/COUNTIF(B11:B23,B11:B23)) 

but I can't figure out how to break it down by location. My original formula is shown below along with my expected result by location. I did try using COUNTIFS but I got a DIV/0 result.

Answer 1

Sum Unique Distinct Values Based On One Criteria

Try this formula as shown in image below,

• Formula used in cell B6

=SUMPRODUCT(IF(FREQUENCY(IF($A$11:$A$23=A6,
MATCH($B$11:$B$23,$B$11:$B$23,0)),
ROW($C$11:$C$23)-ROW($C$11)+1),$C$11:$C$23))

Depending on your Excel version you may need to press CTRL + SHIFT + ENTER & then fill down!

Or, If you are using O365 and presently in Office Insiders Beta Channel Version, then you may try the following formulas as well, where DROP() Function has been used!

=SUMPRODUCT(DROP(UNIQUE(FILTER($A$11:$C$23,A6=$A$11:$A$23)),,2))

Or,

=LET(r,$A$11:$A$23,s,$A$11:$C$23,SUM(DROP(UNIQUE(FILTER(s,A6=r)),,2)))