Improve company productivity with a Business Account.Sign Up

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

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
Asked:
hkgal
  • 3
  • 2
1 Solution
 
NBVCCommented:
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
 
hkgalAuthor Commented:
I am using Excel 2003 that without a countifs function?

the first sentense return:
1
3
2
2
1

seems not correct...
0
 
hkgalAuthor Commented:
e.g.Min for "Asia" is 2, so in column C should be all 2 for Asia
0
 
NBVCCommented:
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
 
hkgalAuthor 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.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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