If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
(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 |
#2
|
|||
|
|||
(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 |
#3
|
|||
|
|||
(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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Question to Bob Phillips (or whoever...) | vezerid | Worksheet Functions | 5 | December 11th, 2005 11:44 AM |
Array formula returning wrong results | TUNGANA KURMA RAJU | General Discussion | 1 | November 19th, 2005 10:29 AM |
Question on Array Formula | John | Worksheet Functions | 1 | February 21st, 2004 05:40 PM |
Excel Array Formula | John Telly | Worksheet Functions | 3 | February 21st, 2004 01:16 PM |
Array Formula - Use of OFFSET function with array argument | Alan | Worksheet Functions | 2 | February 11th, 2004 09:38 PM |