# nested subtotal

Posted by slich on March 16, 2001 11:51 AM

Is there a way to take a list, that is sorted by say column A primary & B secondary, and then generate subtotals:
1) at each change in column B, count B AND
2) at each change in A, count A

Thanks,
Slich

Posted by Mark W. on March 16, 2001 12:18 PM

Slich, consider including an additional column
in your data set that contains the formula,
=A2&B2. You could choose this new column

Posted by Mark W. on March 16, 2001 12:25 PM

...in column A and B are variable length I'd recommend
that you use =A2&","&B2 instead. This will insure
that you'll get a separate subtotal for...

"CA","TS"
"CAT","S"

Posted by Ian on March 16, 2001 12:38 PM

I may be reading this slightly different than Mark. But you can certainily subtotal the data once as you normal would for column A. Then follow the same procedure for column B, but make sure you deselect the Replace Current Subtotal field in the subtotal box.

Posted by Mark W. on March 16, 2001 12:48 PM

Upon further review...

leads me to believe that their isn't a way to
accomplish your request using Excel's built-in
Subtotal command. To paraphrase your question,
"You want to produce a count of the values in
columns A, B when values change in either column."

The closest I could get was with the formula,
=IF(OR(A2&LT;&GT;A1,B2&LT;&GT;B1),N(D1)+1,D1), but Subtotal
would still put a count in the column whose

Posted by Mark W. on March 16, 2001 12:51 PM

Ahh, of course...

...you're right. I made the rash assumption that
he wanted to do it in 1 pass.

Posted by slich on March 16, 2001 1:16 PM

That's it! thanks to both Ian and Mark

Posted by Ian on March 16, 2001 1:19 PM

Re: Ahh, of course...

I'm all for simplicity. This just made me think of a report I prepare that people want to be able to drill down to a varying degree of detail. One of the things I like best about this site is the various solutions proposed to a singular problem.