Unique Ranking with Multiple Criteria

[chrysti]

This is what I am currently using:
=SUMPRODUCT(--($E$2:$E$30000=$E2),--($F$2:$F$30000=$F2),--($O2<$O$2:$O$30000))+1

I am trying to rank store sales by style by units sold...this is working sort of...it won't break ties...I just want to get to my top 10 to 25

If any one can help...thank you in advance!!!

 

[Marcelo Branco]

You are welcome. Glad it helped

M.

 

[ehui03]

Maybe (adjust the ranges to your case)
Excel 2007 or higher

A B C D

Location	Gender	Value	Ranking
1	M	10	2
1	M	10	3
1	M	20	1
1	M	8	4
1	F	5	3
1	F	9	1
1	F	5	4
1	F	3	5
1	F	7	2
2	M	21	2
2	M	21	3
2	M	22	1
2	F	23	1
2	F	23	2
2	F	18	3

<colgroup><col style="WIDTH: 48pt" span="4" width="64"></colgroup><tbody>
</tbody>

Formula in D2
=COUNTIFS($A:$A,$A2,$B:$B,$B2,$C:$C,">"&$C2)+COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2)

copy down

M.

Hi! Thanks for this advice! It worked for me as well but I'm super curious to understand how this formula works. I'm still trying to understand how a countif formula can rank them under multiple criteria?

Ed

 

[kelly mort]

Please I need help.

Have this setup

A B C D E F
math science ICT total min rank

So I am ranking with the min. But when there is a tie I want to break it with the total. Please help

 

[kelly mort]

Please I need help.

Have this setup

A B C D E F
math science ICT total min rank

So I am ranking with the min. But when there is a tie I want to break it with the total. Please help

 

[Marcelo Branco]

Hi! Thanks for this advice! It worked for me as well but I'm super curious to understand how this formula works. I'm still trying to understand how a countif formula can rank them under multiple criteria?

Ed

Hi Ed

COUNTIFS is the best tool to rank. The RANK function is very limited...
To understand the formula try the tool
Formulas > Formula Evaluation

M.

 

[Marcelo Branco]

Please I need help.

Have this setup

A B C D E F
math science ICT total min rank

So I am ranking with the min. But when there is a tie I want to break it with the total. Please help

Try to provide a small data sample - something like 10 rows - along with expected results.

To post a data sample see section B at
https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

M.

 

[kelly mort]

I cannot post the sample I have tried but it's not getting through

 

[AliGW]

You must post a sample if you want help. Try the ForumTools add-in for Excel (there's a link in my signature line).

 

[kelly mort]

Please can I get an email so I can send it to you instead. I am trying to post it here but I am not able.

Thanjs

 

[AliGW]

No - that's against the forum rules. You can't attach a file here, but you could put it on a file-sharing site and post a link to it here.