+ Reply to Thread
Results 1 to 6 of 6

Formula combination that distinguishes cells based on category then returns their totals

  1. #1
    Registered User
    Join Date
    10-20-2015
    Location
    Alabama
    MS-Off Ver
    Excel 2013
    Posts
    29

    Formula combination that distinguishes cells based on category then returns their totals

    I have attempted to combine the SUM + IF functions in hopes of distinguishing between different categories of cells within one range on a spreadsheet and then summing these same categories on a different spreadsheet, but I'm not sure it's even possible much less figured out how to formulate such a function. I'll explain my problem below and have attached sample spreadsheets of my data and my desired outcome.

    The first spreadsheet is the data of product prices, their price adjustments, and categories. Column B contains the price, column C is where I manually enter the adjusted price dependent on the category, and column D is where the categories are differentiated in the column by conditional formatting (FULL is green, DISCOUNT is blue, and COST is red).

    The second spreadsheet is where I want the product prices to be linked to the first spreadsheet, but the problem arise when you attempt to get a total of the prices in column C. The simple way to get a total for each category is to manually select each cell within a category and use SUM (e.g., =SUM(C3,C7:C10) for FULL), but this is incredibly inefficient for numerous products that are recategorized frequently (e.g., a product that is DISCOUNTED one day is FULL price the next day). Also, it should be noted that I need products in the COST category to have their ORIGINAL price (data in column B) summed together. Therefore, what I need is a formula that can recognize the category of each price then return a total (SUM) for each category. This would be a great help to me, because the second spreadsheet would automatically update all I need once the data from the first spreadsheet is correctely summed up!

    PLEASE HELP lol.
    Attached Files Attached Files
    Last edited by TitansGo; 10-21-2015 at 11:59 AM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,576

    Re: Formula combination that distinguishes cells based on category then returns their tota

    You need to look at SUMIF

    For B5
    =SUMIF($H$4:$H$15,B4,$G$4:$G$15)

    For the COST (D5)
    =SUMIF($H$4:$H$15,D4,$F$4:$F$15)

    Does that help?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    10-20-2015
    Location
    Alabama
    MS-Off Ver
    Excel 2013
    Posts
    29

    Re: Formula combination that distinguishes cells based on category then returns their tota

    You are amazing! Yes it works! Thanks!

  4. #4
    Registered User
    Join Date
    10-20-2015
    Location
    Alabama
    MS-Off Ver
    Excel 2013
    Posts
    29

    Re: Formula combination that distinguishes cells based on category then returns their tota

    I have encountered a problem with using this formula. It's works when applied to the way I setup the spreadsheet before, but I had to link the categories in this spreadsheet (from spreadsheet PRODUCT DATA to spreadsheet CATEGORY TOTALS) instead of having the formula to categorize the data in the spreadsheet. It seems that the SUMIF function only recognizes exact value matches, while the copied link of categories only shows the file path and thereby returns a 0 (column H). Is there a way to get around this (another formula is perfectly fine!) with copied links? I've attached the updated files!
    Attached Files Attached Files
    Last edited by TitansGo; 10-22-2015 at 04:11 PM.

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,576

    Re: Formula combination that distinguishes cells based on category then returns their tota

    I don't even see a SUMIF formula in your uploaded sheet.
    It shouldn't matter if file path appears in the link, it will still only look at the value returned (Full, Discount or Cost).

  6. #6
    Registered User
    Join Date
    10-20-2015
    Location
    Alabama
    MS-Off Ver
    Excel 2013
    Posts
    29

    Re: Formula combination that distinguishes cells based on category then returns their tota

    Sorry, I was typing in the formula you gave me wrong. Thank you for all your help!

+ 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's value based off combination of two other cells
    By kosherboy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-10-2014, 10:35 PM
  2. [SOLVED] Macro to loop between cells based on other cell value/category
    By Manish_Gupta in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-18-2013, 09:02 AM
  3. Category Totals Using Lists
    By Liberty42 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-17-2013, 09:34 PM
  4. Replies: 4
    Last Post: 07-16-2013, 07:44 AM
  5. Formula needed to split totals into 2 different cells based on percentage
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-07-2013, 04:55 PM
  6. What can i use to display a category title based on a cells value?
    By smithjon in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-23-2013, 07:35 AM
  7. Category Line X and X,Y Combination Chart
    By John Vieren in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-11-2008, 06:33 PM
  8. Adding category totals
    By Cryptographic_ICE@yahoo.com in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-15-2006, 06:50 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