Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. I agreed to these rules
Join Date
7th November 2006
Posts
7

Rank On Multiple Criteria

I work within the sporting field and wish to rank athetes performances (column Z) based on their date of competition (Column M),and the discipline performed (Column O).
i.e if (M\$2:M\$100=M4) & (O\$2:O\$100=O4) then RANK(Z4,Z\$2:Z\$100). It will work based on one criteria with =SUMPRODUCT(--(M\$2:M\$100=M2),--(Z2< Z\$2:Z\$100))+1 but not if I add a second.

Any help would be appreciated

Excel Video Tutorials / Excel Dashboards Reports

2. Senior Member
Join Date
19th October 2006
Location
Australia
Posts
287

Re: Rank On Two Criteria

I'd hazard a guess that you have forgotten to attach your (small) file, swagman.

G.

Excel Video Tutorials / Excel Dashboards Reports

3. I agreed to these rules
Join Date
7th November 2006
Posts
7

Re: Rank On Two Criteria

Originally Posted by GeorgS
I'd hazard a guess that you have forgotten to attach your (small) file, swagman.

G.
Sorry George, original file was far to big to post. I have attached a modified file, in which I want to rank results in Column G aginst the date of performence (Column A) and boat class (Column D).

Swagman

Excel Video Tutorials / Excel Dashboards Reports

4. Super M‌oderator
Join Date
4th July 2004
Location
Posts
2,371

Re: Rank On Two Criteria

Try...

B2, copied down:

=SUMPRODUCT(--(\$A\$2:\$A\$210=A2),--(\$D\$2:\$D\$210=D2),--(G2<\$G\$2:\$G\$210))+1

Hope this helps!

Excel Video Tutorials / Excel Dashboards Reports

5. I agreed to these rules
Join Date
7th November 2006
Posts
7

Re: Rank On Two Criteria

Originally Posted by Domenic
Try...

B2, copied down:

=SUMPRODUCT(--(\$A\$2:\$A\$210=A2),--(\$D\$2:\$D\$210=D2),--(G2<\$G\$2:\$G\$210))+1

Hope this helps!
Thanks Domenic, worked a treat.

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno