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
when defining your Subtotal parameters.
Posted by Mark W. on March 16, 2001 12:25 PM
If your values...
...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...
Slich, a more careful reading of your question
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<>A1,B2<>B1),N(D1)+1,D1), but Subtotal
would still put a count in the column whose
value hadn't changed.
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.
