[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • 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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

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