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

1. ## 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!  Register To Reply

2. ## 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?  Register To Reply

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

You are amazing! Yes it works! Thanks!  Register To Reply

4. ## 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!  Register To Reply

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

It shouldn't matter if file path appears in the link, it will still only look at the value returned (Full, Discount or Cost).  Register To Reply

6. ## 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!  Register To Reply