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
|
|||
|
|||
I know this is obvious but ..
I'm just not seeing it this morning!
I have 3 columns: Col P16 to P500 contains Project Names Col Q16 to Q500 contains Pricing Mechanism descriptions Col X16 to X500 contains Dates In Col AA16 - AA 500 I want to do the following: AA16 = Last Date for Project Name in P16:P500 where Q16:Q500 "Fixed Price" I can do it with a sumproduct array but it (obviously) sums the dates where the project names and pricing mechs are the same. Many thanks for your help! -- "There are 10 types of people in this world. Those who understand Binary and those who don''t ..." |
#2
|
|||
|
|||
I know this is obvious but ..
Try this array* formula in AA16:
=MAX(IF((P$16:P$500=P16)*(Q$16:Q$500"Fixed Price"),X$16:X$500)) *An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) rather than the usual Enter. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - do not type these yourself. If you need to edit the formula you will need to use CSE again. Hope this helps. Pete On Mar 9, 10:51*am, Bony Pony wrote: I'm just not seeing it this morning! I have 3 columns: Col P16 to P500 contains Project Names Col Q16 to Q500 contains Pricing Mechanism descriptions Col X16 to X500 contains Dates In Col AA16 - AA 500 *I want to do the following: AA16 = Last Date for Project Name in P16:P500 where Q16:Q500 "Fixed Price" I can do it with a sumproduct array but it (obviously) sums the dates where the project names and pricing mechs are the same. * Many thanks for your help! -- "There are 10 types of people in this world. *Those who understand Binary and those who don''t ..." |
#3
|
|||
|
|||
I know this is obvious but ..
SUMPRODUCT(MAX((A1:A7="north")*(B1:B7="high")*C1:C 7))
from another post by Gary's student. Thanks!! -- "There are 10 types of people in this world. Those who understand Binary and those who don''t ..." "Bony Pony" wrote: I'm just not seeing it this morning! I have 3 columns: Col P16 to P500 contains Project Names Col Q16 to Q500 contains Pricing Mechanism descriptions Col X16 to X500 contains Dates In Col AA16 - AA 500 I want to do the following: AA16 = Last Date for Project Name in P16:P500 where Q16:Q500 "Fixed Price" I can do it with a sumproduct array but it (obviously) sums the dates where the project names and pricing mechs are the same. Many thanks for your help! -- "There are 10 types of people in this world. Those who understand Binary and those who don''t ..." |
#4
|
|||
|
|||
I know this is obvious but ..
An alternative .. try something like this, normal ENTER, copied down:
=LOOKUP(2,1/(P$2:P$10=P2)*(Q$2:Q$10"Fixed Price"),X$2:X$10) Adapt the ranges to suit -- Max Singapore --- "Bony Pony" wrote: SUMPRODUCT(MAX((A1:A7="north")*(B1:B7="high")*C1:C 7)) from another post by Gary's student. |
Thread Tools | |
Display Modes | |
|
|