Automated weighted counts in a Pivot Table (Excel)

0

0

I have data for sales people who work with different clients. Sometimes the sales person has multiple deals with the same client. I want to be able to work out the count for each sales person for each client - however, I don't want a multi deal to be added as a whole twice. For instance if sales person A does business with company X twice, I want the count to be 1 and .25 (total of 1.25) rather than 2. Then each additional piece of business with company X get's added again as .25

Kevin

Posted 2015-07-07T18:43:02.917

Reputation: 171

1What's the aspect you're stuck on? The math for the "partial credit" would be: 0.75+0.25*count. – fixer1234 – 2015-07-07T19:02:45.887

@fixer1234 - well the math part sure was the first one I was stuck on (so thanks) - the next is how to get it in the field since I can't edit it.. – Kevin – 2015-07-07T19:11:16.277

You'll need to provide more information about the spreadsheet for that part. For anyone to help, they will need to understand what's there. – fixer1234 – 2015-07-07T19:16:03.763

Answers

0

I'm not sure whether it's possible only in pivot table.

Alternatively you can insert a new column where you calculate a corrected number and sum it in the pivot table. I've used the formula based on fixer1234's comment, but with a more sophisticated one you can get also 1 for first instances and 0.25 for the others.

=0.75/COUNTIFS([Sales],[@Sales],[Client],[@Client])+0.25

enter image description here

Máté Juhász

Posted 2015-07-07T18:43:02.917

Reputation: 16 807

Asked: 2015-07-07T18:43:02.917

Viewed: 363 times

Active: 2015-07-08T11:21:49.113