SUM determined by drop-down list

[RICH1B]

HI, I have a product list with different products in each row,
Column A is the number,
Column B is the Description,
Column C is the Quantity,
Column D is a drop down list of options, linked to L8-L13 (list holder
cells) created with the Data Verification function, and M8-M13 is the price
multiplier matched to the option.
I have M5 as the base price holder cell (so I can change the base without
re-wrighting the entire formula)

Column E is the Total for the Row, based on the Option selected from D
Drop-down list linked to L.

I am having trouble creating the formula for "E" for each row based on the
option selected from the drop down.

Thsi is what I am trying to do, (If D5=L8, then M5*C5*M*, or If D5=L9, then
M5*C5*M10) ans so on down the list of options. Each time I try I get a failed
function or a #NAME? error. I have tried SUMIF, SUMIF S

 

[RICH1B]

Correction to last sentence:
Thsi is what I am trying to do, (If D5=L8, then M5*C5*M8, or If D5=L9, then
M5*C5*M9, or if D5=L10, then M5*C5*M10) and so on down the list of options.
Each time I try I get a failed function or a #NAME? error.
I have tried SUMIF, SUMIF S

Note Items is L8-L13 are Text descriptions
Items in M8-M13 are a decimal value used as a multiplier eg, 1.2, 1.7, 2.1
etc associated with the option in L.

 

[L. Howard Kittle]

I'm unclear if you stated your case correctly...?

<If D5=L8, then M5*C5*M*,

M5*C5*M???*???

<or If D5=L9, then M5*C5*M10)

and here do you mean M5*C5*M9 instead of *M10?

Taking a semi wild guess try this in E2 and pull down:

=$M$5*C2*M8

Where your headers are in row 1 and data is in A2 to D6.

HTH
Regards,
Howard

 

[L. Howard Kittle]

Having read your corrections to your original post after I posted a possible
solution, I would say my suggestion will not do what you are asking. Back
to the drawing boards for me with the new and corrected info.

Regards,
Howard

 

[RICH1B]

Thanks I corrected the last sentence in the 2nd post. or here for easier
reading.

I have a product list with different products in each row,
Column A is the number,
Column B is the Description,
Column C is the Quantity,
Column D is a drop down list of options, linked to L8-L13 (list holder
cells) created with the Data Verification function, and M8-M13 is the price
multiplier matched to the option.

I have M5 as the base price holder cell (so I can change the base without
re-writing the entire formula)

Column E is the Total for the Row, based on the Option selected from D
Drop-down list linked to L.

I am having trouble creating the formula for "E" for each row based on the
option selected from the drop down.

This is what I am trying to do, (If D5=L8, then M5*C5*M8, or If D5=L9, then
M5*C5*M9, or if D5=L10, then M5*C5*M10) and so on down the list of options.
Each time I try I get a failed function or a #NAME? error. I have tried
SUMIF, SUMIF S

Note Items is L8-L13 are Text descriptions
Items in M8-M13 are a decimal value used as a multiplier e.g., 1.2, 1.7, 2.1
etc associated with the option in L.

 

[RICH1B]

Thanks, looking forward to something that works

Having read your corrections to your original post after I posted a possible
solution, I would say my suggestion will not do what you are asking. Back
to the drawing boards for me with the new and corrected info.

Regards,
Howard




.

 

[L. Howard Kittle]

If you want send me a sample workbook. Do not include priority info, rather
an example of your worksheet using dummy info and details on the results you
expect beyond what you have posted so far.

(e-mail address removed)

Regards,
Howard

 

[RICH1B]

I tried to send, but email returned and failed.

If you want send me a sample workbook. Do not include priority info, rather
an example of your worksheet using dummy info and details on the results you
expect beyond what you have posted so far.

(e-mail address removed)

Regards,
Howard




.

 

[L. Howard Kittle]

I did get the attachment but was unable to open it. Perhaps because of the
..xlsx
Said I did not have a program to run the file.

Sorry.

Regards,
Howard

 

[L. Howard Kittle]

For what it's worth, I offered these two solutions to OP's query, both seem
to be working okay.

A worksheet formula:

=IF(ISNA(LOOKUP(D5,$L$8:$L$11,$M$8:$M$11)*$M$5*C5),"",LOOKUP(D5,$L$8:$L$11,$M$8:$M$11)*$M$5*C5)

And a change_event macro.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column <> 4 Then Exit Sub

Dim i As Variant
Dim j As Range
Dim k As Long
Dim l As Long

i = ActiveCell.Value
k = ActiveCell.Offset(0, -1).Value
l = Range("M5").Value

Set j = Range("L8:M11")
i = ActiveCell.Application.WorksheetFunction _
.VLookup(i, j, 2, False)

ActiveCell.Offset(0, 1).Value = i * k * l
End Sub

Regards,
Howard