Rank values with non sequential data & ignore text

Hello
From spending the day trying to find a solution I might be asking too much on this analysis, but thought I'd try you guys and then I'll know for sure whether it's possible.

The data is an analysis sheet which is fed from individual bid sheets and provides a sum on the annual savings vs our current price. One row per item with Annual Savings column for each company.

Although I've got a details on the Best Price, I would like to have the rank for each 'Annual Saving' per company & item.

The columns to 'check' against are non-sequential and there needs to be 'No Quote' for other analysis points within the Sheet

I've tried various Sum/Countif/Isnumber/Indirect/Rank nested formula options to, in this instance get the answer 3, without success.
It's 3 because it is the 3rd highest value out of the submissions received (CJ would be 1, CV would be 2, BX would be 4 & all 'No Quote' would be N/A

There are so many linked formula's in the document I've put in a screenshot to understand whether you think there is any chance of finding a solution as I've got 10 company submissions on 49 items!
I've done added an excel version too

Any guidance would be greatly appreciated
Thanks

Capture.JPG

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

Please paste the following formula into cell B8 and drag down:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Due to the arrangement of the data the following proposal employs two sets of helper columns.
The first set, columns CD:CJ, is populated using direct assignments such as: =A2
The second set, columns CL:CR, is populated using: =IF(ISNUMBER(CD2),SUM(SUMPRODUCT((ISNUMBER($CD2:$CJ2))*($CD2:$CJ2>CD2)),1),"N/A")
The Company Rank columns are then populated using direct assignment such as: =CL2
Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.