Ranking Groups of Combined Positive and Negative Values

Hi Folks
As the title suggests I need to Rank groups of values that may contain a mixture of both positive and
negative values. Some may be all positive and some may be all negative as well.

Workbook attached with sample layout-

Cheers
hammer

Try

in E2

=RANK($D2,OFFSET($A$1,MATCH(A2&B2&C2,$A$2:$A$1000&$B$2:$B$1000&$C$2:$C$1000,0),3,COUNTIFS($A:$A,$A2,$B:$B,$B2,$C:$C,$C2)))

Enter with Ctrl+Shift+Enter
copy down

Nice 1 John, I was going to go the OFFSET rout, but was trying to think of another option

Thanks John
That certainly works in the sample workbook but returns #N/A in the actual-
Could you tell me what the ',3,' in the middle of the formula does please?
I am actually using different reference Cols in the actual workbook but am certain they are adjusted correctly.

Thanks

@Phuocam - Excellent formula! Thank you so very much

@JohnT It is actually a true representation, just that I was inputting it to a different sheet where the layout/s slightly differ. This is why I asked how/why the ,3, worked
because I figured (yes, I do that sometimes) that it was the reason for your formula not working in the sheet I was in!!!

@soledad - Thank you also for your time and effort!

I try to always run with formulas that do not require CSE- but being the dummy I am I cannot remember why

CSE formulas formula can impact performance but unless the volumes are large there is little "practical" difference in performance.

But I like Phuocam's solution: very neat!