# 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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### 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: 8

#### 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
58
Replies
1
Views
86
Replies
1
Views
176
Replies
6
Views
74
Replies
19
Views
119

1,127,098
Messages
5,622,683
Members
415,920
Latest member
ExcelNoob28

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