+ Reply to Thread
Results 1 to 5 of 5

How to make cell tabulate totals from's TOTALS column based on the CATEGORY column...

  1. #1
    Registered User
    Join Date
    12-20-2017
    Location
    Canada
    MS-Off Ver
    2017
    Posts
    17

    How to make cell tabulate totals from's TOTALS column based on the CATEGORY column...

    Hi everybody. I have another one and you've all been exceptional so far.

    I have an invoice chart that has a "Category" column and a "Total (after taxes)" column. At the top of the page I have individual cells in which I need the sum total of each individual category.

    The categories are as follows...
    -Beer
    -Wine
    -Liquor
    -NA Bev
    -Restaurant Supplies

    What formula or formatting do I need to use to make a cell (call it C3) reflect the sum total of each "Total (after tax)" cell, dependent upon the information in the "Category" column.

    ***Note: I need one cell to contain the sum total for both "NA Bev" & "Restaurant supplies".***

    Thanks in advance!

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: How to make cell tabulate totals from's TOTALS column based on the CATEGORY column...

    It would help if you attached a sample Excel workbook, as we haven't necessarily seen your other threads.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as it doesn't work on this forum.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    12-20-2017
    Location
    Canada
    MS-Off Ver
    2017
    Posts
    17

    Re: How to make cell tabulate totals from's TOTALS column based on the CATEGORY column...

    Thanks Pete,

    I wasn't referencing any particular prior threads. It was just a general acknowledgement of how exceptionally helpful this forum has been for me and my career.

    That said, some creative google searching got me the answer I was looking for.

    If anybody is interested, the function I needed was SUMIF, and the final equations looked like this...
    **Column F: "Category", Column I: "Totals (after tax)"**

    =SUMIF($F6:$F53,"Beer",$I6:$I53)
    =SUMIF($F6:$F53,"Wine",$I6:$I53)
    =SUMIF($F6:$F53,"Liquor",$I6:$I53)
    =SUMIF($F6:$F53,"NA Bev",$I6:$I53)+SUMIF($F6:$F53,"Restaurant Supplies",$I6:$I53)

    Cheers!
    Last edited by Natejss; 02-10-2019 at 04:12 PM. Reason: Further clarity needed.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: How to make cell tabulate totals from's TOTALS column based on the CATEGORY column...

    Well, I'm glad you got to the solution yourself. Thanks for the rep (though I didn't really do anything).

    If you had the categories "Beer", "Wine", "Liquor" etc. in a column somewhere, then you could just reference each cell instead of including them explicitly in each formula, and then the formula could be copied down without needing to change it. This wouldn't work for the last formula, though.

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    12-20-2017
    Location
    Canada
    MS-Off Ver
    2017
    Posts
    17

    Re: How to make cell tabulate totals from's TOTALS column based on the CATEGORY column...

    The reason I did it this way, is because the I'm General Manager making sheets that will be used by my supervisors, all of whom are very limited in their Excel acumen. I've made one "Category" column in which each cell has a drop-down data-validation list of options for ease of use and congruity of records.

    Long story short, I'm making the process of spreadsheet-building more difficult and tedious for myself in an attempt to make the final sheets as user-friendly as possible for my team.
    Last edited by Natejss; 02-10-2019 at 04:46 PM.

+ 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. [SOLVED] Formula combination that distinguishes cells based on category then returns their totals
    By TitansGo in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-23-2015, 01:11 PM
  2. [SOLVED] Adding up totals based on criteria from another column
    By mlbdc2012 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-07-2015, 09:02 PM
  3. [SOLVED] Need VBA Macro to make multiple totals based on value of a different cell.
    By smpita in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-27-2014, 01:44 PM
  4. Count totals in one column based on associations with another
    By mrtravizw in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-26-2013, 05:18 PM
  5. [SOLVED] Macro to sort column datasets based on totals
    By Hyflex in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-10-2011, 06:20 PM
  6. Calculating totals in a column based on a lookup in another column
    By Michael Wright via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-15-2005, 05:06 AM
  7. Comparing/matching totals in a column to totals in a row
    By Nicole L. in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-27-2005, 07:06 PM

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