sum until value greater

[trentsim]

I'm looking to do a sumproduct on two arrays of values arranged from smallest to largest (one of shares, the other of the corresponding share prices), but i want the sumproduct to stop when it finds a share price in the array that's bigger than a given value. ie sumproduct(a1:a5,b1:b5) where if, say, b4>d4 (d4 is my given value), then it outputs sumproduct(a1:a3,b1:b3). or if b1>d4, then it outputs nothing.

thanks a lot

[dominicb]

Good afternoon trentsim

Does this help :

=SUMPRODUCT(--(B1:B5<=D4),(A1:A5))

HTH

DominicB

[trentsim]

thanks for the reply -- it seems like what you suggested just tells you the number of cells in the b1:b5 array that are less than d4, rather than giving me the sumproduct of those cells with their corresponding a1:a5 cells.

[dominicb]

Hi trentsim

Oh!
Well, if that's the case try something like this :
=SUMPRODUCT(--(B1:B5<=D4),(A1:A5)*(B1:B5))

HTH

DominicB

[trentsim]

worked like a charm, thank'ee kindly

trentsim