x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 325

# Excel Ranking within a group

Working on excel :

Segment     Gp               Result
2                 Asia                   2
5                 Asia                   2
3                 Asia                   2
6                Regional            1
1                Regional            1

Third result column is based on the result of first two columns:
- For same gp, detect the min value and show

How to do? thanks!
0
hkgal
• 3
• 2
1 Solution

Commented:
Do you mean this?

Eg.  If your data is in A2:B6, try in C2:

=SUMPRODUCT(--(\$B\$2:\$B\$6=B2),--(A2>=\$A\$2:\$A\$6))

or

=COUNTIFS(\$B\$2:\$B\$6,B2,\$A\$2:\$A\$6,"<="&A2)

copied down.
0

Author Commented:
I am using Excel 2003 that without a countifs function?

the first sentense return:
1
3
2
2
1

seems not correct...
0

Author Commented:
e.g.Min for "Asia" is 2, so in column C should be all 2 for Asia
0

Commented:
so you mean...

=MIN(IF(\$B\$2:B\$6=\$B2,A\$2:A\$6))

confirmed with CTRL+SHIFT+ENTER, not just ENTER and copied down.
0

Author Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

• 3
• 2
Tackle projects and never again get stuck behind a technical roadblock.