One Ranking for Multiple Ranges

[AboveBeyond]

Example:

1. 50
2. 75
3. 35
4. =sum(A1:A3)
5. 22
6. 99
7. 48
8. =sum(A5:A7)

It would rank rows 1-3 and 5-7 and not rows 4&8 because I don't want the sums ranked.

Thanks!

 

[Domenic]

If the format is consistent, that is, 3 rows of numbers and a sum, try the following...

B1, copied down:

=IF(MOD(ROW()-ROW($A$1),4)+1<4,SUM(IF(MOD(ROW($A$1:$A$8)-ROW($A$1),4)+1<4,IF(A1<$A$1:$A$8,1)))+1,"")

...confirmed with CONTROL+SHIFT+ENTER

If the format is not consistent, replace...

=SUM(A1:A3)

and

=SUM(A5:A7)

with

=SUBTOTAL(9,A1:A3)

and

=SUBTOTAL(9,A5:A7)

...and try the following instead...

B1, copied down:

=IF(SUBTOTAL(3,A1),SUM(IF(SUBTOTAL(3,OFFSET($A$1:$A$8,ROW($A$1:$A$8)-ROW($A$1),0,1)),IF(A1<$A$1:$A$8,1)))+1,"")

...confirmed with CONTROL+SHIFT+ENTER

Note that both formulas will rank from highest to lowest, and will return the following result...

50 3
75 2
35 5
160
22 6
99 1
48 4
169

Hope this helps!