Ranking if with a countifs formula

[Peter Davison]

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.

 

[jasonb75]

Try

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

 

[Peter Davison]

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

 

[Peter Davison]

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]

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]

No problem thanks