Hi
What I need to do is retrieve the £ sales according to the average lead time bracket (<1 week is express, 1-4 weeks and 4+ Weeks), by week and product type.
It seems like it should be easy enough to do but I can't seem to figure it out and was hoping someone may be able to help.
I've attached an example of the data I'm working with and an example of the rollup sheet.
Thank you in advance!
Hi -
You can use SUMPRODUCT with the OFFSET function to accumulate your values based on the criteria: Apple, Week, and LT Bracket. The following formula is in Cell C6 of your example:
=SUMPRODUCT((Data!$A$3:$A$14=Rollup!$A$1)*(OFFSET(Data!$D$3,0,MATCH(Rollup!C$5,Data!$D$1:$DC$1,0)-1,12)<1)*(OFFSET(Data!$D$3,0,MATCH(Rollup!C$5,Data!$D$1:$DC$1,0),12)))
The first term in SUMPRODUCT, in green, checks to see if the each row matches "Apple". Next, the first OFFSET function, in red, returns a range of cells, 12 cells high, and located however many cells over from D3 that the MATCH function counts based on the week number. So, for example, Week 1 is the very first entry, so Match returns 1 (then I have to subtract 1 to make it 0 for my OFFSET). Likewise Week 2 returns a 2, and so forth. This gives me the column with the LT's for that week, then I compare that to <1 for express, between 1 and 4 and then >=4 as you go down in cells C7 and C8. Finally, the second OFFSET function,in Blue is the corresponding list of values.
Attached is your spreadsheet with the above formulas implemented. You can play around with the Week headings or type in Banana in A1 and you can see the values change.
Hope this helps.
____________________________________________
If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved
If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.
there are a few options here, tbh (incl a Pivot) but...below is a non-volatile SUMIFS approach
type following into A6:A8:
< 1
<= 4
< 1000
these will act as criteria for week test in SUMIFS
with the above in place -- you could use something like:
note: I've assumed you could change Week number in the header, e.g. if you changed C5 to "W21" the results would auto adjust.Formula:
Please Login or Register to view this content.
Hello,
This has worked perfectly, and your explanation has really helped me understand the process.
Thanks so much for your help!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Thanks for the Rep CC!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks