Sort Subtotals in Excel 2

[Stroppy]

Hi there,

I have a spreadsheet that we use for stats on service users. Some of the columns include: Date, FirstName, LastName, etc. I need to report on visits by these people. I can do a basic sort and subtotal, but what I'd like is to sort the subtotals so that they're all sorted into 1 visit, 2 visit etc. I thought I'd be able to create a column "No of visits" and get the subtotal into that column but that didn't work.

I've got a feeling it's probably to do with filtering, and had a look but can't filter the numbers of visits as they are in the surname cells.

I know it's fairly simple so if anyone feels like answering, I'd appreciate that!!

M

 

[SkipVought]

Hi,

What did not work? Did you try the SUMIF function or SUMPRODUCT function. That column needs to had the subtotal in every row for the sort to work.

Would a PivotTable work?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Stroppy]

Hello Skip,

The bit that "didn't work" is this:

1. Sort - by surname
2. Subtotal - add total to surname
3. OK
4. Subtotal ends up either in the same cell as the surname or underneath in a new cell (pretty sure that's it, I haven't got access to it)

So, it works ok, but then I want to sort them so that all of the 1 visits, 2 visits, 3 visits etc are together so I can count how many clients visit 1, 2 or 3 times. That's the bit I can't do.

I thought I could add a column called "No. of visits" and select to have the subtotals in there but couldn't make it work, it ended up with a lot of zeros in it. I'm sure the two forumulas you referred to plus a pivot table may well work but I'm not up to that.

M

 

[SkipVought]

Buck up, mate.

In the first empty column to the right of your table, Head the column Visits (or whatever). I assume that is in row 1.

In row 2 of that column, assuming that surname is in column A and your are counting occurences ...
[tt]
=COUNTIF(A:A,A2)
[/tt]
and copy down thru your data.

Here's a example of the result...
[tt]
Surname Visits
Aston 3
Aston 3
Aston 3
Brown 2
Brown 2
Carr 4
Carr 4
Carr 4
Carr 4
[/tt]
Using Conditional Formatting you could make the font in Visits, WHITE except for the first or last occurence, for instance.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

You can use the SubTotal feature as you were trying to - you just need to add the "Add subtotal to" to a different column.

Example (in 2003 - if you have 2007 let us know):
[ul][li]Select all data[/li]
[li]Go to Data > Subtotals[/li]
[li]At each change in: LastName[/li]
[li]Use function: Count[/li]
[li]Add subtotal to: FirstName*[/li]
[li]OK[/li]
[ul][li]you'll see little numbers appear at the very top left - beside the Column Letters[/li][/ul]
[li]Press 2 to collapse to only the summary rows[/li]
[li]Sort by the column containing numbers, FirstName[/li]
[li]Press 3 to expand all rows[/li][/ul]

Your left with the list sorted as you wish and subtotals after each employee.

* As long as everyone has something in the FirstName field, that will give you the count of how many times they appear.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Stroppy]

Honestly, you guys are great.

Thanks Skip, for the free therapy! And your Excel instructions looks SO easy. Buck up indeed!

And, Another Higgins, thank you for that too. I did that but for some reason didn't like it. I think I didn't select "Count"...I'll check it out.

Thanks for answering such a basic question, M