Excel subtotal heading text & another subtotal in 1 cell

[PWD]

I have a user who wants to put in standard subtotals breaking on customer name BUT also wants to display the number of items that subtotal comes from, i.e. ARDEN WINDOWS LTD subtotal of sales is '=SUBTOTAL(9,C88:C93)' in Column C and the heading in Column B is 'ARDEN WINDOWS LTD'. What they also want to be able to see is the number of items that made up those sales. You can enter '= "ARDEN WINDOWS LTD Total " & SUBTOTAL(2,C88:C93) & " Items"' manually to show 'ARDEN WINDOWS LTD Total 6 Items' but is it possible to do this automatically for each subtotal?

 

[ETID]

Sounds like the pivot table is the tool for this job...

 

[IfThenElvis]

A quick & dirty way would be to put this formula in D4 and copy it down column D. Blanks would show up on non-subtotal rows.
=IF(RIGHT(B4,5)="total",COUNTIF(B:B,LEFT(B4,LEN(B4)-6)),"&quot

Hope this helps.

 

[KenWright]

Assuming you are using Data / Subtotals to subtotal in the first place, one way might be to rerun the data / subtotals changing the sum to count and unchecking the 'replace current subtotals' bit. This will then put an additional line into each section with the count associated with that sum. Not exactly what you asked, but a very easy fix if acceptable.

Regards
Ken...............

 

[ETID]

Try the Pivot table...it's easy once you do it and you'll never mess with code and messy array formulas again for this type of summary!

Very powerfull tool!!!

 

[PWD]

Thanks all. I going to show the user the formula in Column 'A' as the data goes across to Column 'J' and also the 2 x SubTotals - you have to do the 'Count' first and then the 'Sum' gets put above it and that looks better that way araound. I'm also going to have a go at the Pivot Table idea now.