Rank formula based on a exclusion criteria.

**Jabba69** · 08-08-2014, 05:35 AM

Hi
I want to use a Rank formula based on a criteria.

In column A I have a list of Brands (but includes a value of "Other"), in Col B some data (say Unit Sales).

I want to rank a particular brands performance (say its data is in cell C6) against all apart form "Other".

The data is dynamic so I cant just exclude Other, as it will be in a different position depending on slicer selections.

This works but includes "Other":

Formula:

Please Login or Register  to view this content.

I've attached an example file.

Any help would be much appreciated.
Thanks

**samba_ravi** · 08-08-2014, 05:45 AM

=RANK(B6,B2:B10,0)-COUNTIFS(A2:A10,"Other",B2:B10,">="&B6)
Try this

if you use
=RANK(B6,B2:B10,1)
then use RANK(B6,B2:B10,1) - COUNTIFS(A2:A10,"Other",B2:B10,"<="&B6)

**daddylonglegs** · 08-08-2014, 06:04 AM

You can use COUNTIFS on it's own, e.g.

=COUNTIFS(A$2:A$10,"<>Other",B$2:B$10,">"&B6)+1

**samba_ravi** · 08-08-2014, 06:12 AM

GOOD ONE MR daddylonglegs

**Jabba69** · 08-08-2014, 08:39 AM

Thanks both for your help

Rank formula based on a exclusion criteria.

LinkBack

Thread Tools

Rate This Thread

Display

Rank formula based on a exclusion criteria.

Re: Rank formula based on a exclusion criteria.

Re: Rank formula based on a exclusion criteria.

Re: Rank formula based on a exclusion criteria.

Re: Rank formula based on a exclusion criteria.

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] rank based on a criteria

[SOLVED] Rank based on criteria

[SOLVED] Rank based on criteria

[SOLVED] Rank based on criteria

[SOLVED] Rank based on criteria

Bookmarks

Bookmarks

Posting Permissions