That's extremely helpful. So we need to rank D:D if D:D="*Total"?

This is extremely complicated for me without using very complex array formulas - IF functions don't support wildcards, which confounds easy solutions that would generally be in the form of {RANK(D2,IF(A:A="Total",A:A))}....but since you have other things in front of the word "Total" (e.g. Apple Total), this won't work. Similarly, AGGREGATE doesn't have a RANK function capability.

So, in E2, use this:

=IF(COUNTIF(A2,"*Total*")=1,1,0)

Copy it down. That's your helper column formula.

Then in F2, use:

{=RANK(D2,IF(E:E=1,E:E))}

That's an array formula, so enter it with CNTRL+ALT+DELETE