Excel VBA - Counting Negative Values and Ranking Them

[ggmkp]

Trying to count the negative values and ranking them from highest count to lowest

I was going to use the worksheet function like this

Set CountRng = range("B1:F1") 
CountApple = Application.WorksheetFunction.CountIf(CountRng, "<0")

But not sure how I can rank them. Maybe I need to use array here?

Result should rank Orange (3), Banana (2) and Apple (1)

 

[kweaver]

If your data begins in A2 (I considered A1 a header), you can place this ARRAY FORMULA (CTRL+SHIFT+Enter) in B2 and fill down.

=IF($A2<0,MATCH($A2,LARGE(IF($A$2:$A$31<0,$A$2:$A$31),ROW(INDIRECT("1:"&COUNTIF($A$2:$A$31,"<0")))),0),"")

I only had values in A2:A31.

As per Excel's RANK formula, ties will rank equally and the next value will be incremented.