dianamruelas
New Member
- Joined
- Sep 28, 2015
- Messages
- 44
- Office Version
- 365
- Platform
- Windows
Is there a way I can sum unique values in a pivot? I receive a report that forces a few different data sources together. I need the sum of the charge column, but the challenge is that I need only the sum of unique values in the balance column. Here's my table:
For invoice p100, the balance is only $50 total and this is what I would like to display in a pivot. But it is listed multiple times. This would reflect an erroneous balance of $150 in the pivot. Here's the pivot I would like:
invoice | item | charge | balance |
p100 | a | 123 | 50 |
p100 | a | 124 | 50 |
p100 | a | 125 | 50 |
p300 | b | 126 | 25 |
p400 | b | 127 | 30 |
For invoice p100, the balance is only $50 total and this is what I would like to display in a pivot. But it is listed multiple times. This would reflect an erroneous balance of $150 in the pivot. Here's the pivot I would like:
Row Labels | Sum of charge | Sum of UNIQUE balance |
p100 | 372 | 50 |
p300 | 126 | 25 |
p400 | 127 | 30 |
Grand Total | 625 | 205 |