All of the cells in E have a formula to get a number. But if the condition is
false, it returns "". Perhaps that is causing the error?



"Frank Kabel" wrote:

Hi
any chance you have a #VALUE error in your data range or are there text
entries in column E?

--
Regards
Frank Kabel
Frankfurt, Germany


mdoyle13 wrote:
Frank, thanks for the tips and help. The problem that has arisen with
this attempt is #VALUE! error message. The error showed up even when
I broke it down to the minimums of d*e. I did save it as an array, so
I don't think format is the problem. It's just a doozy, it appears.




"Frank Kabel" wrote:

Hi
try the array formula (entered with CTRL+SHIFT+ENTER):
=MAX((B1:B1000="R")*(C1:C1000="H")*(D1:D1000=33)*( E1:E1000))

--
Regards
Frank Kabel
Frankfurt, Germany


mdoyle13 wrote:
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.