(array?) formula
 

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

(array?) formula
 

=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" wrote in message
...
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

(array?) formula
 

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" schreef in bericht
...
=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" wrote in message
...
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