Sum only distinct values by group (data confounded by Cartesian effect)

[timspin]

Hi there - can anyone help. I would usual do this in a pivot table (sum distinct), but as my data is 373,000 rows I dont get the option to add data to the data model when inserting a pivot table.

My data problem looks like this. I would only want to sum a single instance of 122, 5433 and 862 to total 6417,

Col A--Col B
1 ---- 122
1 ---- 122
1 ---- 122
1 ---- 122
2 ---- 5433
2 ---- 5433
2 ---- 5433
3 ---- 862
3 ---- 862
3 ---- 862

Ideally Id like to be able to do this in a pivot, maybe I could create another column C, that I could just SUM in a pivot table (and this is what I need help with)

Col A--Col B----Col C
1 ---- 122 ------122
1 ---- 122
1 ---- 122
1 ---- 122
2 ---- 5433 ---- 5433
2 ---- 5433
2 ---- 5433
3 ---- 862------ 862
3 ---- 862
3 ---- 862

Any ideas - thanks
Tim

 

[SpillerBD]

I can't imagine why you wouldn't be able to add the data to the DataModel. Definitely where to go.
I make the range a table which is easier to make sure it meets the basic requirements before being able to load into the data model.