SUBTOTAL nested in SUMPRODUCT

[hmead]

I cannot figure out how this formula calculates weeks of supply in a spreadsheet I'm working on. The formula below returns the weeks of supply we have on hand today. Columns FV:HN are the weekly demand forecast, with column FV being week 35 (current week). Column CN is the current inventory available.

I mainly don't understand the SUBTOTAL and OFFSET pieces. The SUMPRODUCT is just there to return a zero and avoid negative weeks of supply, I think? But if I remove that part I get the #SPILL! error


=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(FV163:$HN163,,,,COLUMN(FV163:$HN163)-COLUMN(FV163)+1))<=CN163))

 

[Eric W]

Welcome to the MrExcel forum!

The COLUMN(FV163:$HN163)-COLUMN(FV163)+1

part creates an array like {1,2,3,4...} for the total number of columns in FV163:HN163. This is put into OFFSET, in the "number of columns" parameter. So OFFSET converts the range to

FV163, FV163:FW163, FV163:FX163, etc. SUBTOTAL will add up the numbers in those columns. So if FV163 = 1, and FW163 = 2, and FX163 = 7, then the sums will be {1,3,10} and so on for the rest of the columns. The reason that they used SUBTOTAL instead of SUM is because SUM does not like arrays of ranges. then the formula then resolves to:

=SUMPRODUCT(--({1,3,10}<=CN163))

So if CN163 = 8 items on hand, then this turns into

=SUMPRODUCT(--{TRUE,TRUE,FALSE})

The -- converts the TRUES to 1s, and finally the SUMPRODUCT adds up the 1s to get 2. So the sum of the first 2 columns is less than the number on hand.

Excel 365 actually has newer functions that can do this formula easier, but I don't have those functions yet. Perhaps someone else will jump in.