+ Reply to Thread
Results 1 to 3 of 3

Calculating usage of drop-down choices on separate sheet

  1. #1
    Registered User
    Join Date
    09-05-2010
    Location
    Massachusetts (USA)
    MS-Off Ver
    Excel 2007
    Posts
    44

    Question Calculating usage of drop-down choices on separate sheet

    I am trying to get some data tallied real-time on a spreadsheet I created. What I am trying to do is as the sheet is filled out using pre-defined drop-down menu choices on each day listed, the total of each choice used, plus the applicable monetary value attached show up on the tab "Summary by Code".

    In essence, say "Choice 1" which is worth $1.00 is used 3 times on Monday and Tuesday. On the "Summary by Code" tab, I want to see Choice 1 show "3" under "Usage" header/column and $3.00 under the "Total" header/column. Then on Wednesday if it is used again, the total updates to 4 and $4.00 respectively and so on.

    I thought conditional formatting would help, but that apparently isn't what I need. I have no idea where to go from here on than I think some sort of formula is needed. Any help is appreciated. Thank you!

    The file is attached and pre-filled out with examples using Friday and Saturday as test days to illustrate my point. There is a hidden sheet called "Lists" if it's need to see the monetary values of each menu choice.
    Last edited by BlondOIverBlue; 04-27-2011 at 09:14 PM.

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Calculating usage of drop-down choices on separate sheet

    BlondOIverBlue,

    In the Summary by Code sheet, in cell B2, I entered the following formula:
    Please Login or Register  to view this content.


    Then in the Summary by Code sheet, in cell C2, I entered this formula:
    Please Login or Register  to view this content.


    Then I just fillled down. I have attached a modified version of your posted workbook so you can see how it works.

    Hope that helps,
    ~tigeravatar
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    09-05-2010
    Location
    Massachusetts (USA)
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Calculating usage of drop-down choices on separate sheet

    tigeravatar,

    Thank you so much! COUNTIF....I never knew that existed. I'm going to study the code and play around with it so I can get a better understanding on how it works. I also never thought the VLOOKUP would be needed. I learned a lot on this one, thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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