Prev: Count
Next: How do I change the fill color of a cell using an "IF" functio
From: Bony Pony on 9 Mar 2010 05:51
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 ..."
From: Pete_UK on 9 Mar 2010 06:05
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
<bony_ponySPAMS...(a)BLOODYSPAMbtinternet.com> 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 ..."

From: Bony Pony on 9 Mar 2010 06:36
SUMPRODUCT(MAX((A1:A7="north")*(B1:B7="high")*C1:C7))


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 ..."
From: Max on 9 Mar 2010 08:23
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:C7))
> from another post by Gary's student.

 | 
Pages: 1
Prev: Count
Next: How do I change the fill color of a cell using an "IF" functio