Hello. Newbie here. I need help with I think may be a very simple formula that I can not get my head around. I've tried match, sumproduct, countif(s) ... you name it! I've spent the last 2 days searching the internet & find "close" solutions, but not one that will work for me. I have a spreadsheet with about 3000 rows. The spreadsheet is sorted by total sales in descending order. Col A contains 3000 SKU#s. Each SKU # belongs to 1 of 20 distinct "classes". I'm trying to create a formula at the "class" level. I have the 20 distinct classes listed in Col D.
I calculated the sum of sales by distinct class in Col E. Then I calculated 80% of those sales in Col F.
What I need is a formula in Col G that says (for example): How many SKUs does it take (count) to get the "closest" to 80% of their class sales. The # of SKUs can result in > or < 80%- just needs to be the closest.
For example, how many SKUs (rows?) in class 943 does it take to sum to (close to) 10,305,237? Summing from top to bottom of row F?
Any help would be much appreciated And of course it was due "yesterday"
Col A Col B Col C Col D Col E Col F Col G
1. SKU# Class Sales Distinct Class Total Sales 80% of Sales # SKUs to get to 80% of sales
2. 1668443 943 $2,104,337 943 $12,881,546 $10,305,237 ?
3. 760951 943 $1,246,321 732 $2,857,360 $2,285,888 ?
4. 573483 943 $1,213,076 820 $2,809,333 $2,247,466 ?
5. 506790 943 $1,052,498 821 $4,663,223 $3,730,578 ?
6. 958103 943 $988,738 826 $1,369,611 $1,095,688 ?
7. 300525 732 $972,281 828 $2,648,978 $2,119,182 ?
I calculated the sum of sales by distinct class in Col E. Then I calculated 80% of those sales in Col F.
What I need is a formula in Col G that says (for example): How many SKUs does it take (count) to get the "closest" to 80% of their class sales. The # of SKUs can result in > or < 80%- just needs to be the closest.
For example, how many SKUs (rows?) in class 943 does it take to sum to (close to) 10,305,237? Summing from top to bottom of row F?
Any help would be much appreciated And of course it was due "yesterday"
Col A Col B Col C Col D Col E Col F Col G
1. SKU# Class Sales Distinct Class Total Sales 80% of Sales # SKUs to get to 80% of sales
2. 1668443 943 $2,104,337 943 $12,881,546 $10,305,237 ?
3. 760951 943 $1,246,321 732 $2,857,360 $2,285,888 ?
4. 573483 943 $1,213,076 820 $2,809,333 $2,247,466 ?
5. 506790 943 $1,052,498 821 $4,663,223 $3,730,578 ?
6. 958103 943 $988,738 826 $1,369,611 $1,095,688 ?
7. 300525 732 $972,281 828 $2,648,978 $2,119,182 ?