sum product subtotal with offset function

[gand3rson]

Hello new member so thanks in advance for any help I can get <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have an excel formula that I am using to calculated dales sales on hand (a financial ration) that is as follows. I found the formula on the net and I am trying to dissect it/understand how it works. The part of the formula I am having difficulty with is <o></o>
<o></o>
=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(E4:AB4,,,,COLUMN(E4:AB4)-COLUMN(E4)+1))<=D3))<o></o>
<o></o>
I understand that sumproduct with “(--(“ is a way to count cells meeting a specific criteria. In this case the criteria is the number of cells that summed up must be less than cell “D3”. I am just not sure how the subtotal(9) and offset function are being combined to do this. Does anyone have any insight or have a place to point to me where I can read up on combining these three functions?<o></o>
<o></o>
Again much thanks<o></o>

 

[barry houdini]

Hello Marcelo,

That's right

As discussed in this thread, the formulas in the "Daily Dose of Excel" link are slightly different but the principle is the same - the OFFSET function returns an "array of ranges", ...and when you apply SUBTOTAL function to that array, using 9 in SUBTOTAL you get an array of the sums of those ranges, so in the example here where D4 = 30, E4 = 40 and F4 = 60 then this formula

=SUBTOTAL(9,OFFSET(D4,,,,COLUMN(D4:AA4)-COLUMN(D4)+1))

generates

{30,70,130.......}

It would be the same result as a helper row with this formula in D5 copied across:

=SUM($D4:D4)

SUMPRODUCT isn't actually summing those here - it's comparing that array against C3 (100) and returning the number of elements of that array that are <= C3, i.e. 2 (which gives the 2 from 2.5)

 

[Marcelo Branco]

Barry,

Crystal clear for me now! (the example using SUM($D4:D4) is perfect)

Tks very much for the explanation.

M.