(array?) formula

[Jack Sons]

Hi all,

In col E I have dates or text or nothing, in col G is ABC or RST or dates or
text or nothing. and in col H numbers or something else.

I need the (array?) formula that gives me the number in col H for which in
col G is ABC or RST and in col E is the most recent date.

Thanks in advance for your help and my best wishes for a goor 2006.

Jack Sons
The Netherlands

[Bob Phillips]

=SUMPRODUCT(--(E2:E200=MAX(E2:E200)),--(ISNUMBER(MATCH(G2:G200,{"ABC","RST"}
,0))),H2:H200)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Jack Sons" <[email protected]> wrote in message
news:%[email protected]...
> Hi all,
>
> In col E I have dates or text or nothing, in col G is ABC or RST or dates
or
> text or nothing. and in col H numbers or something else.
>
> I need the (array?) formula that gives me the number in col H for which in
> col G is ABC or RST and in col E is the most recent date.
>
> Thanks in advance for your help and my best wishes for a goor 2006.
>
> Jack Sons
> The Netherlands
>
>

[Jack Sons]

Bob,

Thank you for your answer.

If the G-cell corresponding with the E-cell with the most recent date is
anything else than ABC or RST the reult is zero.

In the example below I should get 150 of cell H8 but I get zero. If cell G3
is ABC or RST I get 1760 What's wrong?

Jack.
----------------------------------------
col E col G col H

19-11-2002 RST 145

29-11-2002 ABC 160

31-1-2005 1.760

30-5-2003 ABC 170

1-10-2004 ABC 180

29-10-2004 aaa 185

29-12-2004 ABC 190

28-1-2005 RST 150

20-1-2005 RST 200



"Bob Phillips" <[email protected]> schreef in bericht
news:[email protected]...
> =SUMPRODUCT(--(E2:E200=MAX(E2:E200)),--(ISNUMBER(MATCH(G2:G200,{"ABC","RST"}
> ,0))),H2:H200)
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Jack Sons" <[email protected]> wrote in message
> news:%[email protected]...
>> Hi all,
>>
>> In col E I have dates or text or nothing, in col G is ABC or RST or dates
> or
>> text or nothing. and in col H numbers or something else.
>>
>> I need the (array?) formula that gives me the number in col H for which
>> in
>> col G is ABC or RST and in col E is the most recent date.
>>
>> Thanks in advance for your help and my best wishes for a goor 2006.
>>
>> Jack Sons
>> The Netherlands
>>
>>
>
>