Excel pivot table sum distinct values

Question

I have a question to ask about summing distinct sums. I have a table as below which is coming from a database

ID  |Activity |Rank| Max Rank|Health    Aggregated_$    Tiering   Engaged   Quarter
1   |Activty3 |3   |8        |1            100         4 - Lowest   Y   FY18 Q4
1   |Activty3 |3   |8        |2            100         4 - Lowest   Y   FY18 Q1
1   |Activty4 |4   |8        |1            100         4 - Lowest   Y   FY18 Q4
1   |Activty2 |2   |8        |1            100         4 - Lowest   Y   FY18 Q3
1   |Activty8 |8   |8        |1            100         4 - Lowest   Y   FY18 Q3

I am trying to get the sum distinct $ in each activity. Therefore, what I am trying to achieve is something like this in a pivot. The reason why I have to use the pivot is because Quarter is used as a filter to get only certain quarters (FY 18 and beyond for example)

Activity  |   100
Activity2 |   100
Activity3 |   100
Activity4 |   100
Activity8 |   100

My pivot looks like this now. Was wondering if a sumproduct would work on this?

Distinct is typically associated with counting, not summing. It does not make sense to "sum distinct". To achieve what you describe, you would need to add a column to your data source that identifies the distinct rows. How you would build the logic for that is not clear, though, since all rows in your source seem to be distinct, if all columns are evaluated. — teylyn

Bayabil Getachew Bayabil Getachew · Accepted Answer · 2018-11-07T22:37:19

I would add a column in your data set next to your $ field and do a formula in an adjacent cell to divide the $ by the number of records which meet the criteria; in this case "Activity".

For example, if Aggregate field is in column B and Activity is in Column A, I would type in cell C2this formula =B2/countif($A:$A, $A2). This will divide each value in B2 by the number of times each specific activity occurs, thereby evenly distributing the sum across the number of records.

You can then pivot this new field in values and in essence get the true value for distinct sum. My $.02.

Excel pivot table sum distinct values

1 Answers