Subtotal: Sum & Count :: MrExcel Message Board
 Search   Memberlist   Usergroups   Favorites   Statistics   Register

 Subtotal: Sum & Count

Parra
Board Master

Joined: 22 Feb 2002
Posts: 636
Location: Los Angeles, CA
Flag:

Status: Offline

Subtotal: Sum & Count

I need to subtotal some data, but I need to sum one column and to count another.

Any suggestion on how I can do this.

Thanks

Fri Sep 19, 2003 3:53 pm

Mark W.
MrExcel MVP

Joined: 11 Feb 2002
Posts: 7146
Location: Austin, Texas USA
Flag:

Status: Offline

Re: Subtotal: Sum & Count

See the Data | Subtotals... menu command.

Fri Sep 19, 2003 3:55 pm

Parra
Board Master

Joined: 22 Feb 2002
Posts: 636
Location: Los Angeles, CA
Flag:

Status: Offline

Re: Subtotal: Sum & Count

I know how to use the subtotal, but how do I SUM and COUNT at the same time. Is it possible.

Thanks

Fri Sep 19, 2003 4:16 pm

Mark W.
MrExcel MVP

Joined: 11 Feb 2002
Posts: 7146
Location: Austin, Texas USA
Flag:

Status: Offline

Re: Subtotal: Sum & Count

Not "at the same time". I'd use the Subtotal... command to create sums and then change the 1st argument of the resultant SUBTOTAL function from 9 to 2 as needed.

Perhaps, a PivotTable may work as well.

Fri Sep 19, 2003 4:34 pm

Andrew Poulsom
MrExcel MVP

Joined: 22 Jul 2002
Posts: 8266

Flag:

Status: Offline

Re: Subtotal: Sum & Count

Do it first with SUM on the first column. Then do it again with COUNT on the second column, making sure Replace current subtotals is unchecked.

Or do both columns with SUM and change the first argument for the SUBTOTAL function from 9 to 3 for the column you want to count. You can use Edit|Replace for this.

Fri Sep 19, 2003 4:37 pm

Mark W.
MrExcel MVP

Joined: 11 Feb 2002
Posts: 7146
Location: Austin, Texas USA
Flag:

Status: Offline

Re: Subtotal: Sum & Count

quote:
Originally posted by Andrew Poulsom:
Do it first with SUM on the first column. Then do it again with COUNT on the second column, making sure Replace current subtotals is unchecked.

Andrew, the problem with this approach is the an additional row is inserted into the list.

Fri Sep 19, 2003 4:43 pm

Andrew Poulsom
MrExcel MVP

Joined: 22 Jul 2002
Posts: 8266

Flag:

Status: Offline

Re: Subtotal: Sum & Count

quote:
Originally posted by Mark W.:
quote:
Originally posted by Andrew Poulsom:
Do it first with SUM on the first column. Then do it again with COUNT on the second column, making sure Replace current subtotals is unchecked.

Andrew, the problem with this approach is the an additional row is inserted into the list.

That's why i said "Or ..."

Fri Sep 19, 2003 4:44 pm

Parra
Board Master

Joined: 22 Feb 2002
Posts: 636
Location: Los Angeles, CA
Flag:

Status: Offline

Re: Subtotal: Sum & Count

I have to go to a meeting, I'll work on it when I return.

I appreciate your responses.

Fri Sep 19, 2003 4:51 pm
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First

Forum Jump:
 Jump to: Select a forum MrExcel Forums----------------Excel QuestionsInternational forumMicrosoft AccessTechnical issues and Future development Holy Macro! Books----------------Holy Macro! Products The Lounge----------------Max Cells Lounge Announcements----------------About This BoardHall of Fame WinnersTest Here

Page 1 of 1

Forum Rules:
 You cannot post new topics in this forumYou cannot reply to topics in this forumYou cannot edit your posts in this forumYou cannot delete your posts in this forumYou cannot vote in polls in this forum