Thanks to all. I have tried all of them and keep coming up with the same
result I was getting before, namely it giving me the largest number every
time. Maybe an example of what I'm trying to get will help.

A B C D E
1 R V 11 3
1 R V 24 7
1 P V 7 22
2 R H 20 -2
2 R H 33 17
2 R H 20 21
2 R H 33 4
2 P H 7 13

What I'm trying to do is get the largest number from E when the other
conditions would be R, H and 33. The result should be 17, but I'm always
getting 22. For R,H and 20, I want the result to be 21, but it is coming up
22.

Did I mention this would be reading from about 2000 rows? That's why I'm
looking for a formula to extract the largest for each condition rather than
having to go through each row for multiple conditions.

Thanks again.

"Frank Kabel" wrote:

Hi Bob
it also does not work for me :-))
lets exchange a test file <vbg

--
Regards
Frank Kabel
Frankfurt, Germany


Bob Phillips wrote:
Tom,

It works for me. I only did it because Frank's respomnse didn't work
for me (sic!)

This data

A B C D E
1 text1 text2 5 10
1 text1 text2 5 15
1 text1 text2 6 25

gives me 15.

confused RP


"Tom Ogilvy" wrote in message
...
You must have left something out Bob. That doesn't work.

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
or




=MAX(--(A1:A10=1),--(B1:B10="text1"),--(C1:C10="text2"),--(D1:D10=5)*E1
:E10)

still array entered, Ctrl-SHift-Enter.

Change the values to suit.

--

HTH

RP

"Frank Kabel" wrote in message
...
Hi
try the following array formula (entered with cTRL+SHIFT+ENTER):
=MAX(IF(A1:A10="cond1")*(B1:B10="cond2")*(C1:C10=" cond3"),E1:E10))

or the array formula
=MAX((A1:A10="cond1")*(B1:B10="cond2")*(C1:C10="co nd3")*(E1:E10))


--
Regards
Frank Kabel
Frankfurt, Germany


mdoyle13 wrote:
I had posted a counting and summing question here a couple weeks
ago and received a great answer and introduction to the
sumproduct
function. I'm looking for one more item, this one extracting the
largest number from a range but only if conditions are met for
other ranges.

The help I received before with the sumproduct function allowed
me to count and sum results from cell ranges e1:e10 when
conditions were true in ranges a1:a10, b1:b10, c1:c10 and d1:d10.
So if a4, b4, c4 and d4 conditions were true, i was able to count
e4 and sum e4. Same for row 6, etc.

What I'm looking for is a way to identify the largest number in
e1:e10 when the first four conditions are true. Thus, in my
previous example, if e4 was 10 and e6 was 6, I want my result to
be 10, even if e8 [which should be ignored if there is a false
condition in a8, b8, c8 or d8] is 15. I have tried working MAX or
LARGE into some IF functions, but it would always return the
largest number [i.e., 15 from e8] in the whole range rather than
the ranges with true conditions.

Is what I'm looking for possible? Did this make sense?

Thanks in advance.