MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Cumulative Sum by a single formula & more (for Samkiat)

Posted by Aladin Akyurek on May 12, 2001 1:57 AM


Here is your sample data which occupy G6 (stock) to N6 from the thread at 16724.html:


The following formula produces an array of cumulative totals/sums. The formula itself is NOT array formula.


This is the array result:


The problem that you want to solve is as you stated:

"Suppose product A has 35 units in stock.

Followings are ordering quantity of product A:
Day 1 : 10
Day 2 : 20
Day 3 : 2
Day 4 : 10
Day 5 to 31 : also has its ordering amounts.

Since product A has 35 units, so it will take 4 days to be out-of-stock. (10+20+2+10 > 35)

I need to answer that it will take 4 days and this formula should be in only one cell.

I beleive that I need to get an array of cumulated stock ordering."

The following array-formula will do what you want:



The first arg of SUBTOTAL which is 9 means "SUM".
The second arg of SUBTOTAL builds the ranges over which to sum. In this example you get:


Note 1. That is MATCH that requires this formula to be entered as an array-formula.

The last arg of match generates an approximate location of the cumulative order amount that is the closest to the stock amount. The last part of the formula, which is 1, together with the location number generates the number of days when you will be ("most likely") out of stock. The result will be correct for the range of 1 to 30 days. If the cumulative amount of orders in 31st day is less than the stock amount you'll get 32 as result.

Note 2. This formula, as you'd guess, replaces your megaformula constructed with IFs.


PS. You need to hit CONTROL+SHIFT+ENTER at the same time (not just ENTER) to anter an array-formula (I suppose you already knew this).

Posted by Somkiat on May 12, 2001 9:37 PM

Thank you so much

Wow, you have made me wake up from a long bad dream. (I try to find this answer for many nights.)
Thank you very much,


Posted by Somkiat on May 13, 2001 4:43 AM

A revised formula

I have built a new array formula from your great idea.


B3 is amount of stock
E3:AI13 are ordering amounts in each day

It works and thank to you again.


Posted by Aladin Akyurek on May 13, 2001 5:11 AM

Re: A revised formula

SUM against MATCH+TRANSPOSE... Not bad. Not bad at all.