1 IF function for a range of data

  • Thread starter Thread starter momiage
  • Start date Start date
M

momiage

I have a table that looks like this:

Gender Worth
M 1000
M 500
F 2000
F 750
F 245
M 575
F 2000

What I want to know is:

Is it possible to use *one* IF function to choose only the females fro
the gender, and then somehow extract which one of the worths is th
highest? I can do it easily with multiple IF statements in anothe
column, then use the MAX function, but I want to do it with just on
statement. Any help would be appreciated
 
one way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=MAX(IF(A2:A8="F",B2:B8,""))
 
With that data in columns A and B
=SUMIF(A2:A8,"F",B2:B8)
or
=SUMPRODUCT((A2:A8="F")*(B2:B8))
the latter can be extended, say in column C you had another criteria
=SUMPRODUCT((A2:A8="F")*(C2:C8="Another Criteria")*(B2:B8))
and so on will narrow the search further,
Regards,
Alan.
 
Misunderstood you, apologies,
Alan.
Alan said:
With that data in columns A and B
=SUMIF(A2:A8,"F",B2:B8)
or
=SUMPRODUCT((A2:A8="F")*(B2:B8))
the latter can be extended, say in column C you had another criteria
=SUMPRODUCT((A2:A8="F")*(C2:C8="Another Criteria")*(B2:B8))
and so on will narrow the search further,
Regards,
Alan.
Click to expand...
 
Back
Top