Formula Help Please

[Guest]

Hi,
Here is a clip from the data I need to select from.

11-Jun IP D
12-Jun IP D
12-Jun IP D
13-Jun IP D
14-Jun IP D
14-Jun IP D
16-Jun IP D
17-Jun IP D
18-Jun IP D
18-Jun IP NG
18-Jun SP D

I need to write a formula that returns only the the most recent (highest)
date from column A, if the corresponding entry in column B equals "IP". In
other words, I want to know the date of the most recent entry of "IP" in
column B.

Does anyone have a solution for my problem?

Thanks,

 

[Ron Rosenfeld]

Hi,
Here is a clip from the data I need to select from.

11-Jun IP D
12-Jun IP D
12-Jun IP D
13-Jun IP D
14-Jun IP D
14-Jun IP D
16-Jun IP D
17-Jun IP D
18-Jun IP D
18-Jun IP NG
18-Jun SP D

I need to write a formula that returns only the the most recent (highest)
date from column A, if the corresponding entry in column B equals "IP". In
other words, I want to know the date of the most recent entry of "IP" in
column B.

Does anyone have a solution for my problem?

Thanks,

=MAX((A1:A11)*(B1:B11="IP"))

This must be entered as an **ARRAY** formula. After typing in the formula,
hold down <ctrl><shift> while hitting <enter>. Excel will place braces {...}
around the formula.
--ron

 

[Rick Rothstein \(MVP - VB\)]

=MAX((A1:A11)*(B1:B11="IP"))

This must be entered as an **ARRAY** formula. After typing in the
formula,
hold down <ctrl><shift> while hitting <enter>. Excel will place braces
{...}
around the formula.

If I am not mistaken, the following "non-array entered" formula will also
work

=SUMPRODUCT(MAX((A1:A1000)*(B1:B1000="IP")))

Rick

 

[Guest]

Ron,
This is interesting. How does the second part of the formula work?
MAM

 

[Ron Rosenfeld]

Ron,
This is interesting. How does the second part of the formula work?
MAM

The second part returns TRUE or FALSE which, in a multiplication, Excel will
interpret as 1 or 0.


--ron

 

[Ron Rosenfeld]

If I am not mistaken, the following "non-array entered" formula will also
work

=SUMPRODUCT(MAX((A1:A1000)*(B1:B1000="IP")))

Rick

Yes, it should.
--ron