Rank excluding rows where a specific cell equals zero.

robgoldstein

Board Regular
Joined
Oct 26, 2013
Messages
165
Office Version
  1. 2019
Platform
  1. Windows
I am trying to use a rank formula, but I want to exclude rows where a cell equals 0.
The equation I am using is =rank(Q3,$Q$3:$Q$17,false)
The cell I am looking to exclude is 'Toronto Playground _A_ 12U Spring 2023 Stats (2)'!BF3 If it equals zero.
So instead of being ranked out of 16 which is all the rows, it would only be ranked out of 10 if 6 rows contained zero in that BF cell

thanks
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Try this:

=IF(Q3=0,"",COUNTIFS($Q$3:$Q$17,">"&Q3,$Q$3:$Q$17,"<>0")+1)
 
Upvote 0
Change to:

=IF(Q3=0,"",COUNTIFS($Q$3:$Q$17,"<"&Q3,$Q$3:$Q$17,"<>0")+1)
 
Upvote 0
=IF(Q3=0,"",COUNTIFS($Q$3:$Q$17,"<"&Q3,$Q$3:$Q$17,"<>0")+1)
Thanks so much. The only flaw with this is that if 2 cells have equal values they are both the same, so we can get 1,1,2 instead of 1,1,3 any ideas?
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,313
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top