# Conditional Ranking in Excel

Hi

I have a sheet of data, sheet A, that contains columns headers: OrgName, Subject, WiderGroupFlag, Score.

WiderGroupFlag is a Y or N depending on whether the organisation is a member of a wider group.

Now on a separate sheet, sheet B, I have the same structure but only the data that relates to one OrgName, the one I want to rank by score by subject amongst other OrgNames on Sheet A that are members of the wider group.

Is this doable?

Thanks,

A.
dlmille

Yes.  Can you provide a sanitized example workbook to demonstrate?

Let's assume column A has the organization, and column B has the value to be ranked, then the formula would look something like this (assuming data from row 1 to 100):

=SUMPRODUCT((\$A\$1:\$A\$100=A1)*(B1>\$B\$1:\$B\$100))+1

You could paste that, for example, in column C and copy down.

Multiple conditions can be tested for using this approach.

Dave
SOLUTION
byundt

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Brad, I hope we meet someday.  I'd like to buy you a pint.  In fact, I'm going home to my family and will pop one open and cheers to you.

Nite all.

Cheers,

Dave
AndrewMcLaughlin

When I enter this formula as an array I get a Name error - an ideas why.

=SUMPRODUCT((RG="Y")*(subject=\$B3)*(\$C3<MEDIAN))+1

Thanks,

Andrew
There is no need to enter that SUMPRODUCT as an array.  SUMPRODUCT already is an array function.

MEDIAN is an Excel function, so you might want to rename that, however, you're probably getting the NAME error because RG or Subject or MEDIAN is not defined.  Please check to see that they are defined in your name manager.

Can you upload a sample, if more help is needed?

Dave