# Microsoft Office Application Help - Excel Help forum > Excel Charting & Pivots >  > [SOLVED] PivotTables - Sum Distinct Values

## TheMacroNoob

Hello all,

I am trying to use PivotTables to summarize and filter data. I have various tables that all do what I want, except for one.
I have a list of *RMs (People)*, their *Units Under Management*, and *Total Claim Cost per RM*. Some properties have *multiple claims*, thus repeating data such as *Unit count*.
(A property with 100 units might have 3 claims, making the PivotTable display 300 units.)

Is there any way to make a PivotTable sumdistinct, or something similar, to display the RM, their units under management (counting the units for a property only once), and their total cost?

The reason I want this information is to simply divide the total incurred by units for a claims per door column. Currently it would be using inaccurate unit numbers unless I create another data set. 

Please advise if you know the solution off the top of your head. Thank you very much.
*Please see attached for the excel file with data and examples.*

Problem Example.xlsm
Screenshot 2022-08-17 111818.png

----------


## oeldere

Sheet Data 
Cell W3=IF(COUNTIF($B$3:$B3,$B3)=1,$R3,"")

After that a pivot table.

See the attached file (sheet PT Oeldere)

----------


## TheMacroNoob

Hello,

You, my friend, are amazing. It is a shame to have to add another column but this is a great solution to get around the PivotTable. 

Thank you very much!

----------


## oeldere

Thanks for the rep.

Glad I could help.

Thanks for marking the question solved.

----------

