Count of # SKUs it takes to get to 80% of the sales for a specific criteria

HollyBB

New Member
Joined
Dec 20, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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 ?
 

Attachments

  • MrExcel.PNG
    MrExcel.PNG
    47.9 KB · Views: 31

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
I had to pop out of dynamic array size matching at the end and resort to iferror(xxx,""). I hate when that happens. I'll be interested if fluff can fix it.

MrExcelPlayground15.xlsx
ABCDEFG
2SKUClassSalesDistinct ClassesTotal Sales80%# SKUS to reach 80%
34650943$2,100,000943$21,483,223$17,186,57810
41498943$2,011,236732$5,800,202$4,640,1623
54644943$1,996,827820$4,293,616$3,434,8932
68369943$1,906,509821$7,492,032$5,993,6254
73341943$1,893,101826$5,419,367$4,335,4944
83034732$1,885,222828$1,172,189$937,7511
98143820$1,880,716 
102758943$1,830,752 
119052821$1,813,968 
128446943$1,812,024 
134900821$1,732,512 
143193732$1,644,815 
156470943$1,577,623 
168875821$1,478,944 
174978820$1,382,242 
181621943$1,315,552 
198286943$1,249,300 
209954732$1,183,279 
216626821$1,181,805 
223329826$1,108,543
239888943$1,107,104
246122732$1,086,886
257201943$1,080,239
264638826$1,045,946
274353826$1,028,388
286563826$936,418
291169828$849,867
304565820$763,270
315829943$670,779
323455821$629,542
331056943$626,775
348909821$529,394
353550826$462,337
363262826$419,264
372742826$418,471
388836828$322,322
397978820$267,387
403736943$212,020
414369821$125,867
426815943$93,384
Sheet2
Cell Formulas
RangeFormula
D3:D8D3=UNIQUE(B3:B42)
E3:E8E3=SUMIF($B$3:$B$42,D3#,$C$3:$C$42)
F3:F8F3=0.8*E3#
G3:G21G3=IFERROR(LET(a,FILTER($C$3:$C$42,$B$3:$B$42=D3),b,SEQUENCE(ROWS(a),ROWS(a)),c,IF(MOD(b-1,ROWS(a))+1>=INT((b-1)/ROWS(a))+1,1,0),d,MMULT(SEQUENCE(1,ROWS(a),1,0),c*a),e,ABS(d-F3),MATCH(MIN(e),e,0)),"")
Dynamic array formulas.
 
Upvote 0
Thank You for your response. I only encountered 1 issue: I'm running out of resources (there are actually 6k rows). Any other work-around?

1671560161024.png
 
Upvote 0
The arrays made must get too big. 3000 records is too many for it.
 
Upvote 0
New helper column in D:

MrExcelPlayground15.xlsx
ABCDEFGH
2SKUClassSalesrunning classDistinct ClassesTotal Sales80%# SKUS to reach 80%
34650943$2,100,000$2,100,000943$21,483,223$17,186,57810
41498943$2,011,236$4,111,236732$5,800,202$4,640,1623
54644943$1,996,827$6,108,063820$4,293,616$3,434,8932
68369943$1,906,509$8,014,572821$7,492,032$5,993,6254
73341943$1,893,101$9,907,672826$5,419,367$4,335,4944
83034732$1,885,222$1,885,222828$1,172,189$937,7511
98143820$1,880,716$1,880,716
102758943$1,830,752$11,738,424
119052821$1,813,968$1,813,968
128446943$1,812,024$13,550,448
134900821$1,732,512$3,546,480
143193732$1,644,815$3,530,037
156470943$1,577,623$15,128,071
168875821$1,478,944$5,025,424
174978820$1,382,242$3,262,959
181621943$1,315,552$16,443,623
198286943$1,249,300$17,692,923
209954732$1,183,279$4,713,316
216626821$1,181,805$6,207,229
223329826$1,108,543$1,108,543
239888943$1,107,104$18,800,027
246122732$1,086,886$5,800,202
257201943$1,080,239$19,880,266
264638826$1,045,946$2,154,489
274353826$1,028,388$3,182,877
286563826$936,418$4,119,295
291169828$849,867$849,867
304565820$763,270$4,026,229
315829943$670,779$20,551,045
323455821$629,542$6,836,771
331056943$626,775$21,177,819
348909821$529,394$7,366,165
353550826$462,337$4,581,632
363262826$419,264$5,000,896
372742826$418,471$5,419,367
388836828$322,322$1,172,189
397978820$267,387$4,293,616
403736943$212,020$21,389,839
414369821$125,867$7,492,032
426815943$93,384$21,483,223
Sheet2
Cell Formulas
RangeFormula
E3:E8E3=UNIQUE(B3:B42)
F3:F8F3=SUMIF($B$3:$B$42,E3#,$C$3:$C$42)
G3:G8G3=0.8*F3#
H3:H8H3=MATCH(MIN(ABS(FILTER($D$3:$D$42,$B$3:$B$42=E3)-G3)),ABS(FILTER($D$3:$D$42,$B$3:$B$42=E3)-G3),0)
D3:D42D3=SUMIF(B$3:B3,B3,C$3:C3)
Dynamic array formulas.
 
Upvote 0
How about
Fluff.xlsm
ABCDEFG
1
2SKUClassSalesDistinct ClassesTotal Sales0.8# SKUS to reach 80%
346509432100000943214832231718657810
414989432011236732580020246401623
546449431996827820429361634348932
683699431906509821749203259936254
733419431893101826541936743354944
8303473218852228281172189937750.91
981438201880716
1027589431830752
1190528211813968
1284469431812024
1349008211732512
1431937321644815
1564709431577623
1688758211478944
1749788201382242
1816219431315552
1982869431249300
2099547321183279
2166268211181805
2233298261108543
2398889431107104
2461227321086886
2572019431080239
2646388261045946
2743538261028388
286563826936418.2
291169828849866.5
304565820763270.4
315829943670778.7
323455821629542
331056943626774.6
348909821529393.9
353550826462336.5
363262826419264.4
372742826418471.3
388836828322322.1
397978820267386.7
403736943212019.8
414369821125867.2
42681594393383.52
Main
Cell Formulas
RangeFormula
D3:D8D3=UNIQUE(B3:B42)
E3:E8E3=SUMIF($B$3:$B$42,D3#,$C$3:$C$42)
F3:F8F3=0.8*E3#
G3:G8G3=LET(s,SCAN(0,FILTER($C$3:$C$100,$B$3:$B$100=D3),LAMBDA(x,y,x+y)),XMATCH(MIN(ABS(F3-s)),ABS(F3-s),0))
Dynamic array formulas.
 
Upvote 0
Another option that will spill down in col G
Excel Formula:
=DROP(REDUCE("",SEQUENCE(ROWS(D3#)),LAMBDA(a,b,LET(s,SCAN(0,FILTER($C$3:$C$100,$B$3:$B$100=INDEX(D3#,b)),LAMBDA(x,y,x+y)),VSTACK(a,XMATCH(MIN(ABS(INDEX(F3#,b)-s)),ABS(INDEX(F3#,b)-s),0))))),1)
 
Upvote 0
How about

Thank You SOOOOO much :) This works GREAT! Could I add one more criteria to the question. If I added an additional column (H) which contained the words either "Active" or "Inactive", could you adjust your existing formula to only look at the "Active" SKUs? (ignore those rows that contained "Inactive" in column H ????
 
Last edited by a moderator:
Upvote 0
Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,315
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top