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

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)