Is this component included? Ok then add to total cost.

[CatSqueezer]

Hello helpful people,

# I need an Excel 2000 formula that puts a total cost together based on the costs of specific components each with its individual cost.

To decide which specific components are needed, l will indicate along a row, with a "Y" the components that are needed, each component having its own column.

For example, in Row 2, l will indicate by inputting "Y" wherever the named components in Column M, O, R, T, U, W, or X are needed.

Where there is a "Y", l want the corresponding cost of that component to be added to the target cell i.e. the Total Cost cell. Let's call this cell "TARGET".

# The corresponding costs are as follows:
a "Y" in $M2 = corresponds with the component cost in ---> $AJ$2
a "Y" in $P2 = corresponds with the component cost in ---> $AJ$3
a "Y" in $S2 = corresponds with the component cost in ---> $AJ$4
a "Y" in $U2 = corresponds with the component cost in ---> $AJ$5
a "Y" in $V2 = corresponds with the component cost in ---> NULL (no corresponding cost)
a "Y" in $X2 = corresponds with the component cost in ---> $AJ$6
a "Y" in $Y2 = corresponds with the component cost in ---> $AJ$7

Remember: Row 2 for columns M-Y contains a "Y" if that component is to be added to the total cost, and that's the only case we are interested in: if any of the components in columns M-Y are needed, i.e. are "Y".

# There is a twist: ONLY if BOTH U2 and V2 = "Y", then add $AJ$5 to TARGET, otherwise don't add $AJ$5 to TARGET. For example, if U2=Y, but V2 is blank, or if U2 is blank but V2=Y, then the corresponding cost for U2 (which is AJ5) is NOT added to the total cost in TARGET.

# As you can see, the total cost in TARGET can vary, depending on which cells in Row 2 have a "Y" in them.

So, is there a formula that does all of this in one cell, the TARGET cell?

I'm thinking along the lines of: IF(any of the cells M2, P2, S2, U2, V2, W2, X2, Y2, Z2) = "Y", THEN (add the corresponding cost referenced from the AJ section, to create one grand total cost)

[cffndncr]

Can you upload a sample of your workbook? That will make it much easier to come up with a solution

[CatSqueezer]

I'll make it simpler

# Columns MPSUVWXY each represent a different ingredient of a burger. Different combinations of these columns gives a customised burger.

# Columns MPSUVWXY correspond to prices in AJ2 to AJ7, each column has a corresponding price in AJ, because each column represents a component, e.g. salad, onions, gherkins. AJ = the area where the price of salad, onions, gherkins etc. are listed.

# Columns MPSUVWXY may / may not have a "Y" in them.

# Wherever there is a "Y", add that column's corresponding cost in the AJ area, to the final cost of the burger being created.

# So l need a formula that gives the total cost of the burger, based on which columns have a "Y" in them.

[CatSqueezer]

P.S. The title of the thread sums the situation up quite nicely!

[cffndncr]

I understand, but uploading a sample still makes it easier for the people that are trying to help you! It helps to see what format the file is in. We can throw formulas at you all day that won't work because we aren't able to see what file we are working with.

For example; what is in columns NOQRT? This will impact what solutions we can come up with.

[CatSqueezer]

Columns NOQRT don't matter, hence they were left out. They do not have any bearing on the situation