# Ranking if with a countifs formula

#### Peter Davison

##### Board Regular
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?

### 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
Try

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

#### Peter Davison

##### Board Regular
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
208.8 KB · Views: 9

#### Peter Davison

##### Board Regular
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
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"))

#### Peter Davison

##### Board Regular
No problem thanks

Replies
9
Views
199
Replies
1
Views
94
Replies
1
Views
306
Replies
6
Views
99
Replies
19
Views
139

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.

### Which adblocker are you using?

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

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