Max value with selection

  • Thread starter Thread starter Frank
  • Start date Start date
F

Frank

A B C D E
F G
fname lname age married
frank smith 40 yes
smith yes
john smith 21 no

mary jones 44 no

tom jomes 33 yes

sam smith 35 yes




44

=SUMPRODUCT(MAX((B2:B7=F2)*(D2:D7=G2),(C2:C7))) why does
this yield 44 and not 40?

Got this from someone else and would think it would work but a test
seems to not work. Want I want returned is the oldest person who's
lname = "smith" and is married, i.e. result I want is 40 but get 44


Thanks,

Frank
 
Frank,

Try this variation

=SUMPRODUCT(MAX((B2:B7=F2)*(D2:D7=G2)*(C2:C7)))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

A B C D E F G
fname lname age married
frank smith 40 yes smith yes
john smith 21 no
mary jones 44 no
tom jomes 33 yes
sam smith 35 yes



44

=SUMPRODUCT(MAX((B2:B7=F2)*(D2:D7=G2),(C2:C7))) why does this yield 44 and not 40?


Got this from someone else and would think it would work but a test seems to not work. Want I want returned is the oldest person who's lname = "smith" and is married, i.e. result I want is 40 but get 44


Thanks,


Frank
 
Try this

=MAX(IF((B2:B7=F2)*(D2:D7=G2),C2:C7))

entered with ctrl + shift & enter

your formula does not work because you are using max on a text range
it might look like

MAX({FALSE;FALSE;TRUE;FALSE;TRUE;FALSE})
 
Actually, I was wrong. It will work given the right adjustments see Bob's
formula
 
Back
Top