+ Reply to Thread
Results 1 to 9 of 9

Getting sum totals of amounts in cells next to drop-down list (validation)

  1. 01-21-2011, 11:14 PM #1
    MadMyke
    MadMyke is offline
    Registered User
    Join Date
    01-21-2011
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    5

    Getting sum totals of amounts in cells next to drop-down list (validation)

    Hi all,

    In advance I'd like to say thank you very much for your help, I've read through a number of posts trying to find thr answer to my problem and I saw some really helpful responses from everyone, but none that I could relate exactly to my issue. I am a fairly novice user so I do apologise if this has been covered elsewhere and I just haven't recognised it.

    I have a spreadsheet (attached) which I want to use for accounting purposes. I have a list of dates with a corresponding cost for the expense, a drop-down list for the type of expense and a secondary drop-down for a second type of expense filtering I use. I want to find a way to add the sum of all the costs of a particualr type of expense. For example if I have 100 entries of expenses and 30 of those are selected as "Phone bill" from the drop-down I'd like an automatic way of totalling all items which are listed as "Phone Bill". If I then change one of those 30 to another expense I'd like the 'Phone bill" total to automatically update itself accordingly.

    Thanks again!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-21-2011, 11:33 PM #2
    stanleydgromjr
    stanleydgromjr is offline
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Getting sum totals of amounts in cells next to drop-down list (validation)

    MadMyke,

    Welcome to the Excel Forum.

    On your atached workbook, there is no worksheet containing the raw data, for the formulae to be created on worksheet 2010 - 2011.

    Please attach another workbook containing that raw data worksheet.
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
    Reply With QuoteRegister To Reply
  3. 01-21-2011, 11:38 PM #3
    MadMyke
    MadMyke is offline
    Registered User
    Join Date
    01-21-2011
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Getting sum totals of amounts in cells next to drop-down list (validation)

    Hi Stan, maybe I'm doing something stupid, but I've just re-checked the file I uploaded and the data for the drop-down is on the second sheet (titled 'Data'). On that sheet there are two columns of titles which are used in the drop-downs on the main sheet (title 2010-2011).

    If I've missed something please let me know.

    Thanks for your help
    Last edited by DonkeyOte; 01-22-2011 at 04:33 AM. Reason: removed unnecessary quote
    Reply With QuoteRegister To Reply
  4. 01-21-2011, 11:53 PM #4
    MadMyke
    MadMyke is offline
    Registered User
    Join Date
    01-21-2011
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Getting sum totals of amounts in cells next to drop-down list (validation)

    I've just attached an updated version of my original spreadsheet, this one has more raw data in it, I think this is what you were asking me for. I've also added areas where I am hoping the sum totals will appear.

    Thanks again!
    Last edited by DonkeyOte; 01-22-2011 at 04:33 AM. Reason: removed unnecessary quote
    Reply With QuoteRegister To Reply
  5. 01-22-2011, 12:02 AM #5
    Marcol
    Marcol is offline
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Getting sum totals of amounts in cells next to drop-down list (validation)

    I can't see an updated workbook sample.

    Try this workbook based on your original attachment.

    1/. Fill in some data to your table
    Note tha the Dorpdowns are dynamic so you can add to the Data Lists as required, keep the lists continuous.

    2/. Select the catagories you want to sum from the Yellow drop-downs

    Hope this helps
    Attached Files Attached Files
    Last edited by Marcol; 01-22-2011 at 12:05 AM.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
    Reply With QuoteRegister To Reply
  6. 01-22-2011, 12:11 AM #6
    MadMyke
    MadMyke is offline
    Registered User
    Join Date
    01-21-2011
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Getting sum totals of amounts in cells next to drop-down list (validation)

    Thanks a lot Marcol!

    That is great, can I also modify this slightly by being able to display the total for "phone bills" which are marked (via the secondary drop-down) as "sales" only? (I would then also create a total for "phone bills" which are marked as "photography" only too.

    Thank you
    Last edited by DonkeyOte; 01-22-2011 at 04:33 AM. Reason: removed unnecessary quote
    Reply With QuoteRegister To Reply
  7. 01-22-2011, 12:33 AM #7
    Marcol
    Marcol is offline
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Getting sum totals of amounts in cells next to drop-down list (validation)

    I think it does that. (Drag B3:C3 down to extend the validation)

    The Orange cell (H8) is perhaps misleading, change the formula to this
    Please Login or Register  to view this content.
    This produces a total where Both selections are true
    i.e.
    Please Login or Register  to view this content.
    Am I missing something?
    Attached Files Attached Files
    Last edited by Marcol; 01-22-2011 at 12:39 AM. Reason: Added attachment
    Reply With QuoteRegister To Reply
  8. 01-22-2011, 12:42 AM #8
    MadMyke
    MadMyke is offline
    Registered User
    Join Date
    01-21-2011
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Getting sum totals of amounts in cells next to drop-down list (validation)

    Fantastic! Sorry, my error I didn't read that output the right way.

    Thanks so much for your help (and thanks to you too Stan), wish you both a great weekend!

    ********************************** PROBLEM SOLVED **********************************
    Last edited by DonkeyOte; 01-22-2011 at 04:33 AM. Reason: removed unnecessary quote
    Reply With QuoteRegister To Reply
  9. 01-22-2011, 12:50 AM #9
    Marcol
    Marcol is offline
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Getting sum totals of amounts in cells next to drop-down list (validation)

    Happy to have helped

    In future please don't quote whole posts in your replies, the moderators don't like it....
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1