2 level subtotal result weird

[HFLo]

refer to below sample.
I do a subtotal on column ID1, then subtotal again on ID2.
the result looks weird:
row 11 shows b total and row 12 shows y total, it should be the other way
round.
same apply to row 16 (c total) and row 17 (y total) etc.

pls advice. thanks !ID1 ID2 amt
a x 1
b x 2
b x 3
b x 4
b y 5
b y 6
c x 7
c y 8
d x 9
d y 10

 

[Ms-Exl-Learner]

If you want to Total the C1 to C11 values based on two criteria that is the
text “B†in A1 to A11 and the text “Y†in B1 to B11 then use the below one:
=SUMPRODUCT((A1:A11="B")*(B1:B11="Y"),(C1:C11))

The below one will check the character B in A1:A11 and character X in B1:B11
and get the total from C1:C11
=SUMPRODUCT((A1:A11="B")*(B1:B11="X"),(C1:C11))

Like the above change the text in the above formula to get your desired data.

Instead of mentioning the value in the formula you can refer it on some
other cell and get the data by mentioning the values in the particular cells.
See the below one
=SUMPRODUCT((A1:A11=D1)*(B1:B11=E1),(C1:C11))
Now Mention the Text in D1 and E1 to get the totals.

Remember to Click Yes, if this post helps!

 

[Jim Thomlinson]

What you have described is the default behaviour of subtotals. Since ID2 is
your second subtotal it shows up after (below) ID1. If I was doing that I
would have used a pivot table to render this...

Sum of amt
ID1 ID2 Total
a x 1
a Total 1
b x 9
y 11
b Total 20
c x 7
y 8
c Total 15
d x 9
y 10
d Total 19
Grand Total 55


Which I think is in line with what you want...