Dynamic Cumulative in a Range

From the description, it sounds like SUMIFS would work for you. We could do better if we had a sample workbook to look at.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

This proposed solution adds a helper table (AK:AX) populated by the array entered formula*:

Formula:

Please Login or Register  to view this content.

The array entered formula* that populates column S (sold) is:

Formula:

Please Login or Register  to view this content.

*Array entered formulas are activated by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Note: The conditional formatting applied to the helper table is not needed, however it may help to explain from where the numbers come, as in:
Green -- Sales table
Blue -- Sales table corresponding to values of one on the Delivery Schedule table
Pink -- Delivery Schedule table
Let us know if you have any questions.

excelnewbie2407 welcome to the forum.

Without some data reference to tell what day(s) (see Items 3 and 5) are relevant I don't see how a formula will accomplish what you describe.

Can you tell us what the determining facts are that lead you to choose those particular days or supply a lookup table enabling a match of the criteria?

I don't really see what the overall objective is. I get the concept that you want to see if production is in line with sales. But how does limiting the sales between delivery dates accomplish this? There is also an ambiguity here. I see a production figure but I don't know what it represents. Is that per month, per week or some other time period?

Also where do you define the delivery dates? Is it in another table somewhere?

If I had this data, I would ignore the delivery dates - you are not associating a quantity with the delivery anyway. I'd use the whole range to get the two-week total for sales and see how that stacks up against the two-week production numbers.

The data are somewhat "lumpy" (some days you have no sales and others you have 20) so using weekly or bi-weekly totals smooths out some of the lumps. I'd also keep a history to see if there are any trends or if the data are seasonal. I might also look for "internal" trends such as sales by day of week - this may impact on when I schedule my deliveries. Heavy weekend sales may make a Friday or Monday delivery desirable depending on the nature of the product (perishable - I want it on Friday to have as much fresh, on hand for the weekend : non-perishable I want it on Monday to resupply myself for the rest of the week.)

I am still not sure I have the concept. Try this formula (in column T(?))

Formula:

Please Login or Register  to view this content.

Are these the numbers you expect?

Now I am catching it. Is the data you presented just for one week or is it an accumulation of weeks. I would suggest getting a quarter's worth of data for the sales. This means 13 data points for each day of the week which should be enough to do fairly accurate trending. You can find the mean (average) and standard deviation and then apply statistics so you can have a 90% confidence level or 95% confidence level or whatever you are comfortable with that you will deliver the right amount of product for the day of the week.

You can develop profiles for each product and sort them by day of the week requirements. For example Item 1 may peak on Tuesdays and Fridays and Item 2 on Monday and Friday and Item 3 on Tuesday only. You can group products by day of the week and back track for lead time. I notice that some items require packing while others don't. This sorting can be done on a "heuristic" based on what percentage of sales occurs on a given day. You may determine that 33% or better of sales on a given day warrants a delivery on the previous day. If nothing breaks the 33% line then the date with the highest percent prevails. If it's a virtual tie, go with a day that has deliveries but the least amount of deliveries.

There will be some manual tweaking as you shift things around.

In any event, we need more than one week's worth of data to feel confident about this.

Here is a rough idea of what I described above. I have a lot of helper columns here.

Column R finds the biggest value for the week.

Column S is the day of the week associated with this value.

Column T is the second largest value.

Column U is the day of the week associated with this value.

Column V is the total number for the week.

Column W is for you to fill out. For example, The best days for delivery for Item 1 are Friday and Saturday. It doesn't make much sense to deliver on two consecutive days, so roll them both into Friday. Item 2 indicates Tuesday and Saturday. You will have to make a judgement call based on Columns S and U.

Column X and Y and Z and AA are used to split the deliveries based on what percentage of sales occur on those days. Column Y is the amount to deliver on the smaller of the days. Column X is the amount to deliver on the bigger of the days (everything that isn't delivered in the other delivery). if there is only one delivery, deliver the total on that day,.

I can see one tweak to add. For the last item on the list, there is a tie for the day with the most sales. The current model finds the first occurrence. The model has to be modified that in the case of a tie, the second delivery day is based on the second occurrence of the value. I'm not doing that now.