software

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
=SUMPRODUCT(--(E2:E200=MAX(E2:E200)),--(ISNUMBER(MATCH(G2:G200,{quot;ABCquot;,quot;RSTquot;}
,0))),H2:H200)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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