CODEWITHSUNDEEP
excelexcel-formulacumulative-sumranking-functions
JCOLE
JCOLE

Reputation: 1

Excel Formula Help - Cumulative Book of Business

I was hoping to get help with the attached workbook. The end goal is to be able to flag the Customers that make up up to 70% of the rep's cummulative activity. I've done this manually in column H but would like a solid formula to get this accomplished. I'm was thinking about a stack like an Offset/Indirect/Cell"Address"/Index/Match/Match/Countifs or CountA would work but am a bit befuddled. The original source data is in columns B-D and is made up of a PowerPivot.

Any ideas through PowerQuery would be welcomed as well and I can provide any details necessary for that.

THANK YOU VERY MUCH!!!

Jordan

Workbook Screenshot

Upvotes: 0

Views: 41

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34180

You can try a fairly simple pull-down formula starting in H3:

=IF((SUMIF(B$3:B3,B3,D$3:D3)-D3)/SUMIF(B$3:B$20,B3,D$3:D$20)<0.7,"Y","")

enter image description here

(doesn't give the same results as you for ABAKER because I don't have complete data for this rep)

Upvotes: 1

Related Questions