Excel subtotal problem

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

gocrazy

Thread Starter
Joined
Jan 6, 2011
Messages
1
I'm having some problems with the subtotal function on Excel. (Windows 7, MS Office ... the most recent one; the computer's brand new.)

I'm keeping track of my check ledger. Each check payment I make is entered into a spreadsheet. I have about a thousand entries. They look something like this:

1235 - 2/14/10 - Home Depot - Personal - Home Remodeling - (45.00)
1236 - 2/14/10 - Office Depot - Business - Office Supplies - (60.00)
1237 - 2/17/10 - Bob - Business - Accountant - (1000.00)
1238 - 2/17/10 - Home Depot - Personal - Home Remodeling - (34.00)
1239 - 2/19/10 - Office Depot - Business - Office Supplies - (20.00)

So let's say I want to see all of the business expenses I paid by check last year with subtotals by type of expense. This is what I do: I go to the fourth column and filter out everything but "Business." Then I go to the second-to-last column and sort by alphabetical order, to group similar expenses together. I go to Data > Subtotal, and select that under each change in the second-to-last column I want a subtotal to be added.

This sometimes works as expected and sometimes not. When it doesn't, Excel seems unable to recognize that all "Home Remodeling" expenses are of the same type. It will add a subtotal after each "Home Remodeling" Item, or after two or three "Home Remodeling" items, or whatever. It seems very random.

I've checked and all of my labels are identical. I honestly don't know how to begin troubleshooting this problem. Can someone help?
 
Joined
Jul 1, 2005
Messages
8,546
Welcome to the board.

"I'm having some problems with the subtotal function on Excel."

There's a difference between subtotal, the worksheet function and subtotal, the ... err ... "tool".

Pedantry aside, You're having problems when you go to "Data > Subtotal". So try something else. Something better, even.

You want to analyse your data by (i) year (ii)" type" -- use a pivot table then. See from B3 of the "Pivot" sheet in the attached that you can flip between "All", "Business", "Personal" way easier. :)


(a "dynamic" named range allows for additional data on Sheet1, just remember to right-click the pivot table and select "Refresh Data" to update)
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top