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

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.

CodePudding user response：

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