Ranking if with a countifs formula

Peter Davison

Board Regular
Joined
Jun 4, 2020
Messages
182
Office Version
  1. 365
Platform
  1. Windows
Hi,
I have a column with sales figures (column F) and a column with list of stores stocking (Column Q). Even though there is sales in Column F against a product I want it to exclude form the ranking if Column Q has a 0 or a 1 in the cell (So less than 2).
I've tried an if formula -
IF(OR(Q12<2,F12=0),"",RANK(F12,F$12:F$1000,0))
whilst that empty's the ranked cell the number ranking isn't re-ranked, so it's still considered in the total ranked products.
I tried to use countifs but could not work out the correct formula.
Would you be able to guide me on what I need to do please?
Thanks for your help.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,643
Office Version
  1. 365
Platform
  1. Windows
Try

=IF(OR(Q12<2,F12=0),"",COUNTIFS(F$12:F$1000,">="&F12,Q$12:Q$1000,"<2"))
 

Peter Davison

Board Regular
Joined
Jun 4, 2020
Messages
182
Office Version
  1. 365
Platform
  1. Windows
Thanks for the above, it does put a blank in the cells that have column Q less than 2, but puts a zero in all other cells that are above 1 in column Q. My ranking column where the formula resides is Column H (if that is any help.
Any other thoughts.
I've added a picture of the first few rows where I copied the formula to (just the ones with 0 in Column H)
Thanks
 

Attachments

  • Ranking.JPG
    Ranking.JPG
    208.8 KB · Views: 9

Peter Davison

Board Regular
Joined
Jun 4, 2020
Messages
182
Office Version
  1. 365
Platform
  1. Windows
I just tried this formula and whilst it still doesn't eliminate the ranking number it does rank the rest of the range.
If there is a way of eliminating the ranked number for the blank cell that would be great.

=IFERROR(IF(OR($Q10<2,F10=0),"",IF($F10<>0,RANK($F10,$F$12:$F$1000), RANK($F10,$F$12:$F$1000)-COUNTIF($F$12:$F$1000,0))),"")

Cheers
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,643
Office Version
  1. 365
Platform
  1. Windows
Sorry, that was my fault, I had brainfart, it should have been as below (last part needs to be >= not < as I used previously).

=IF(OR(Q12<2,F12=0),"",COUNTIFS(F$12:F$1000,">="&F12,Q$12:Q$1000,">=2"))
 

Watch MrExcel Video

Forum statistics

Threads
1,129,890
Messages
5,638,857
Members
417,055
Latest member
SerenaCruz

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
Top