1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel subtotal problem

Discussion in 'Business Applications' started by gocrazy, Jan 6, 2011.

Thread Status:
Not open for further replies.
  1. gocrazy

    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?
     
  2. bomb #21

    bomb #21

    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)
     

    Attached Files:

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/972966

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice