Avatar of AndrewMcLaughlin
AndrewMcLaughlin
 asked on

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.
Microsoft Excel

Avatar of undefined
Last Comment
dlmille

8/22/2022 - Mon
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.

If you load an example I can help you implement, if more assistance is needed.


Dave
SOLUTION
byundt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
dlmille

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

ASKER
Hi Brad/Dave,


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
Your help has saved me hundreds of hours of internet surfing.
fblack61
dlmille

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