Hi Richard

Glad to have helped.

I may be wrong but AFAIK the SumProduct formula and SUM(Array) formulae are
usually fairly similar. By habit I tend to use the SUM(Array) formula when
there are "Min" or "Max" requirements - I haven't tested whether you can use
the SUMPRODUCT formula in this context but I do not think you can.

Regards from London, UK.

--
XL2002
Regards

William



"Richard Buttrey" wrote in
message ...
| William,
|
| This is extremely good. I've learned a lot more today. Thanks for all
| your help.
|
| Just as a matter of interest, what's the essential difference between
| a sumproduct formula, and an =sum {} array formula, which seem to do
| much the same sorts of things?
|
| Kind regards,
|
| Richard
|
| On Wed, 11 Aug 2004 13:32:30 +0100, "William"
| wrote:
|
| RBRichard
| RB
| RBGiven that codes will be repeated in your table as you move down
| column B, I
| RBdo not think what you want is what you asked for. I may be wrong
| :-)
| RB
| RBInstead, here is a suggestion.....
| RB
| RBI have 5 columns and in cells A1 to E1, I have the following column
| RBheadings...
| RBDate
| RBCode
| RBIssues
| RBReturns
| RBNet
| RB
| RBI named the range from A2 downwards "da", the range B2 downwards
| "cd", the
| RBrange C2 downwards "is", the range D2 downwards "re" and the range
| E2
| RBdownwards "ne".
| RB
| RBAssuming yout Returns column (columnd D) has negative numbers,
| enter the
| RBfollowing formula in cell E2 and drag it down as far as necessary.
| RB=SUMPRODUCT((da<=A2)*(cd=B2)*(is+re))
| RB
| RBIn cells H2 downwards, I placed a unique list of all codes.
| RBEnter this "array" formula in cell I2 and then drag down alongside
| the code
| RBnames in column H.
| RB{=IF(MIN(IF(cd=H2,IF(ne<0,da)))=0,"",MIN(IF(cd= H2,IF(ne<0,da))))}
|
| __
| Richard Buttrey
| Grappenhall, Cheshire, UK
| __________________________