Sum Prices of Unique Items with Several Conditions

Question

Level 1

4 points

Sum Prices of Unique Items with Several Conditions

Hello all!

I have a sheet that I'm using to do some small business calculations (personal use for a hobby). In one table that contains "recipes", and one of those columns contains "capital items" (tools, molds, etc) and an adjacent column for the price of the respective capital item. Another column in the same table gives the recipe to which that item belongs.

In another table, I'm calculating the costs of bundles of these recipes. I want to calculate the total capital cost of a bundle, but I don't want to double-count capital items that are used in multiple recipes in the bundle. I only have to buy the tool once! So I want to calculate only the cost of items with unique names.

Here's an outline of the columns involved:

A: Name of the bundle

B: Name of the recipe in the bundle

C: Name of the capital item

D: Price of the capital item

E: Name of the recipe to which the capital item corresponds

I have a formula in a single cell, with a respective value in A (lets say A1). I want it to sum values in D for which the respective value of C is a unique value who's corresponding value of E is one of the values in B with a respective value of A1 in A.

I don't expect anyone to invent this for me, but pointing me in the right direction would be a big help!

Posted on Dec 2, 2022 10:50 PM

Reply

Answer 1

Ralf-F

Level 4

2,741 points

Dec 3, 2022 2:15 AM in response to AidanW64

I think it would really help if you could post a few examples of your table.

That would make it easier to understand when something should be included and when not.

Others may be better in understanding your description, but I have a hard time to understand what you would need.

Ralf

Reply

Answer 2

AidanW64 Author

Level 1

4 points

Dec 3, 2022 12:16 PM in response to Ralf-F

So, here's a screenshot of a simplified version of what I'm doing with some dummy names and prices (to be specific, I'm calculating the costs of making resin pieces for board games).

The top "FORMULA HERE" should output 100 + 22 + 10, are the prices of the unique capital items in the recipes in a Variety Pack. The Variety Pack has Large Token, Small Red Token, Small Blue Token, so I would want to check the Capital Items in all three recipes and sum the price of each only once.

Reply

Answer 3

Badunit

Level 7

31,002 points

Dec 3, 2022 8:05 PM in response to AidanW64

I'm still not quite getting what you want. If the first "Formula Here" is the sum of 100 + 22 + 10, it appears to be the sum of the items for a large token and nothing else. A variety pack would be large mold, small mold, mixing cup, and scraper (100 + 10 + 22 + 10).

One way to solve the problem would be to have an additional category in the Recipes table for "Variety Pack" which would have all the items you need for a variety pack. In the general case, a "variety" recipe might need multiples of some items (such as if the molds were one-time use or to speed up the job by doing things in parallel). Or you could use a column of checkboxes to indicate what items to include in a variety pack. Either one would make for a simple SUMIF formula.

Reply