+ Reply to Thread
Results 1 to 5 of 5

Aggregate totals for columns by category vs budget

  1. #1
    Registered User
    Join Date
    04-23-2014
    Location
    Galway
    MS-Off Ver
    Excel 2003
    Posts
    3

    Aggregate totals for columns by category vs budget

    Hi,

    I'm looking to build a really simple budget management spreadsheet. I've attached an image of how I'd see the finished article look. I'd like columns with categories and prices and then on the right hand side a simple summary of each of the categories, the original budget and then to colour code if I am over or under budget for that category.

    - I can manage setting up the categories via Data Validation
    - I can get the dynamic summary of categories using UNIQUE (I think!)
    - However I cannot get all of the totals for each category and then the status, ie +5 or -3 with colour red or green.

    Any help appreciated, thanks
    Paul

    excel.png

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,878

    Re: Aggregate totals for columns by category vs budget

    Have a look at the attached. Is this of any use to you?

    A simple SUMIF and some Conditional Formatting and job done.

    Let me know if you need any of it explaining.

    BSB.

  3. #3
    Registered User
    Join Date
    04-23-2014
    Location
    Galway
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Aggregate totals for columns by category vs budget

    Hey, thanks for that.

    3 things,

    1) this seems to work only if the categories are in the order they appear now. If I update say B2 from category A to category C the update is not reflected in the summary because the SUMIF range is fixed. I was hoping to have dynamic calculation across the rows so I can update any category or cost and the update would be seen in the summary.
    2) new categories can be added and they have to dynamically appear in the summary. Once budget is entered for it, it should calculate the Running Total and Status (Red/Green)
    3) I have the Category implemented as a dropdown list via DataValidation (just in case that makes a difference)

    Here is the spreadsheet Budget.xlsx
    Last edited by FuzzyFrog; 04-23-2014 at 09:42 AM.

  4. #4
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,878

    Re: Aggregate totals for columns by category vs budget

    1) Auto Calculation may be turned off in my example spreadsheet. Switch that back on and it should calculate changes live.
    2) In your original post you said you had a method for doing this, so I didn't put the effort in. If that's not the case then you'll have to leave it with me for a while as I'm about to leave work and wont have access to Excel until I get home in a couple of hours.
    3) No, that will make no difference. I didn't do that as you said you already had that sorted.

  5. #5
    Registered User
    Join Date
    04-23-2014
    Location
    Galway
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Aggregate totals for columns by category vs budget

    Re: 1) Auto Calculation is set to Automatic in the spreadsheet
    Re:2&3) No worries

    Thanks for your help,
    Paul

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 03-24-2014, 12:37 PM
  2. Category Totals Using Lists
    By Liberty42 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-17-2013, 09:34 PM
  3. Replies: 4
    Last Post: 08-28-2011, 09:24 PM
  4. Sum budget totals depending on the month
    By sengelbrecht in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-07-2007, 02:31 PM
  5. [SOLVED] YTD Budget Totals
    By Dewayne in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-05-2005, 01:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1