Help:Highlight if top 80% cumulative percentage of a product has less than 3 customers

Hi. Use this as the CF formula:

=COUNTIFS($A$3:$A$20,$A3,$E$3:$E$20,"<.8")<3

Don't use whole culmn references in the CF formula. It may get very slow...

No... CF is Conditional formatting.

On my sheet... with the cursor set anywhere in your data range, if you go to Home/Conditional Formatting/Manage Rules/Edit you will see the formula in place.

What you need to do is to change the ranges to something sensible and appropriate to your dataset.

=COUNTIFS($A$3:$A$20000,$A3,$E$3:$E$20000,"<.8")<3

Here (above) it is changed to look at 20,000 rows. I have changed it to 20,000 in the attached sheet. Unless you have close to 1,000,000 rows, do not use:

=COUNTIFS($A:$A,$A3,$E:$E,"<.8")<3

which will look down all 1,000,000+ rows, will increase file size and may impact on performance.

To calculate col E automatically, use:

=SUMIF($A$3:A3,A3,$C$3:C3)/SUMIF(A:A,A3,C:C).

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.