Ranking with exclusions

Hi,

I’m trying to rank the values in column B but excluding from the calculation any values associated with the names “Apple”, “Pear” or “Grape” in column A.

The formula I have is:

=IF(A4="Apple","",IF(A4="Pear","",IF(A4="Grape","",COUNTIFS(A:A,"<>Apple",A:A,"<>Pear",A:A,"<>Grape",B:B,">"&B4))))+1

However, there are #VALUE results for “Apple”, “Pear” and “Grape”, when I would have expected blanks.

Can someone please suggest what I’m doing wrong?

Also, is it possible to amend this formula to deal with ranking duplicate values in column B?

Thanks!