Another way that 'might' make speed up the formula method slightly (not sure how we all managed to miss this until now)
Using the sample file from post #31.
In H3, fill doen to end
=D3<>"Cancelled"
In F3, fill down to end (based on Glenn's suggestion of using sumproduct in place of array).
=IF(D3="Cancelled",0,SUMPRODUCT(MAX(($C$3:$C$1002<C3)*
$H$3:$H$1002*($E$3:$E$1002=E3)*$B$3:$B$1002)))
By using the helper column, the element of the array that checks for cancelled invoices can be reduced from ($D$3:$D$1002<>"Canceled") which requires 1000 calculations, to the single calculation in the helper cell.
The number of 'calculations' in the formula could be open to interpretation in many ways, in my way of looking at this formula, this slight change should reduce the number of calculations per formula by around 16-17%.
Bookmarks