How to get subtotal ignore subtotal which is netted in If()

[yurble_vn]

Hi All,

I have used subtotal to sum a range as below:
Cell1: If(criteria, subtotal(Cell2:Cell6), sum(range)) --> subtotal
Cell2: If(criteria, subtotal(Cell3:Cell6), sum(range)) --> sum
Cell3: If(criteria, subtotal(Cell4:Cell6), sum(range)) --> sum
Cell4: If(criteria, subtotal(Cell5:Cell6), sum(range)) --> sum
Cell5: If(criteria, subtotal(Cell6:Cell6), sum(range)) --> sum
Cell6: subtotal(cell1:cell5)

my purpose is make the function consitent. But the subtotal in cell1 ignore to sum the value in cell2. I suppose it considered these cells are using subtotal function, so it dont count that into its sum.

Is there anyway to solve this?
thanks and best regards,

[Bob Phillips]

Try

=IF(A2=4,H2+J2+L2,SUM(A2:OFFSET(A3,0,0,B2,1)))

[yurble_vn]

I try this, but the reason I use subtotal is: this will be repeated.
back to the very first point is: I have 4 level of number. 1>2>3>4
If the cells is level 4, then it is sum of another rnage
if the cell is level 3, if is sum of all level 4 belong to it
if the cell is leve 2, if is the sum of all level 4 belong to it (or you can say, all level 3 belong to it)
and the same for level 1