IF Statement using several columns

[klcmomy]

I want to write an if statement that will allow me to pull information from
several columns and give me one result at the bottom of my sheet. Example:

IF column D has a b and column E is greater that 0 I want the highest number
from column D

Also I want to write an IF statement that will determine which group in
column E has the highest point total and so on (basically standings 1st, 2nd,
3rd, etc)

Any assistance would be greatly appreciated!

 

[Luke M]

Your problem is missing several pieces of information. When you say column,
do you mean "a b ANYWHERE in column D", or are you checking a specific row?
Also, what are these "groups" you mention in column E. While it may appear
obvious to you, many details are no so clear when you try to describe them

I think for you first problem you want something like this:
=IF(SUMPRODUCT((D2100="b")*(E2:E100>0))>0,MAX(D),"Criteria not met")
or
=IF(AND(D2="b",E2>0),MAX(D),"Criteria not met")

For your 2nd problem, again, not sure what you are trying to describe. I
would recommend looking at the RANK function, as that appears to be somewhat
similar to what you are describing.

 

[klcmomy]

Col D is a level b=beginner, i=intermediate, a=advanced.
Col E is total points
I want to determine if column D has a b in it and the points in col E are
greater than 0 I want the result to post in the column I am putting the
formula in however I want the number that is the largest to get the result of
who is in first place. I am then going to do the same formula for col d with
an a or an i.

I understand what you are saying and it helped but I still can't figure out
how to write the formula.

 

[Ashish Mathur]

Hi,

Try this formula - D22 has B and E22 has 0

=MAX(INDEX((D1620=D22)*(E16:E20>E22)*(F16:F20),,1))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com