Subtotaling Problems (Excel incorrectly grouping items)

[Guest]

I am having trouble with subtotaling in Excel. I'm having trouble finding a
way to clearly describe the problem, so I'll just give a very simple example:

I have two columns, one is "Letter" and the other is "Number." I put
letters in the "Letter" column and put various numbers in the "Number" column
to correspond with them. But then I add numbers that don't have a letter
associated with them. For example,
Letter Number
A 3
B 5
B 1
3
A 1
C 6
A 4
C 2
1
4

I sort the worksheet so that all A's are grouped together, all B's are
together, all C's are together, and all of the numbers without a
corresponding letter are together. However, once I go to do a subtotal, the
numbers without a corresponding letter are included in the subtotal for C
(since C is the last header listed when sorted).

Why is it grouping the undesignated group with C? Is there any way to get
it to do subtotals for A, B, C, and the undesignated group separately?

 

[Peo Sjoblom]

I have no problems grouping by empty cells, however I don't know how you can
sort empty cell below C since if you sort your example either it would start
with the empty cells or it would start with C and end with the empty cells.
I have no problems using subtotal though



--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[John James]

Hi Bobby,

You are correct in your description of the problem (as well as in how
Excel sorts).

After sorting, perhaps you could copy a period (".") into the blank
cells and this should give the correct totals. A bit messy but gives a
result.

Alternatively, you might choose not to use data-subtotals and have a
summary total area using a pivottable or using formulae like::
=SUMIF($A$2:$A$11,"c",$B$2:$B$11)
=SUMPRODUCT($B$2:$B$11,--($A$2:$A$11="c"))