Assigning Numerical Values to Letters and Multiplying Them

[DNAguy]

I have two columns with the possible letter entries of "L", "M", and "H"; the numerical values for each will be 1, 3, and 5, respectively. I want to multiply the values in those two columns, with the numerical value displayed in a third column. I have modified the following formula - =SUM(COUNTIF(H4:I4,"L")*1+COUNTIF(H4:I4,"M")*3+COUNTIF(H4:I4,"H")*5) - and replaced SUM with PRODUCT, as well as replacing the "+" with "*", both to no avail. I would appreciate any assistance on this issue. Thank you.

[tigeravatar]

DNAguy,

Welcome to the forum!
In the original formula, it is redundant to have both Sum() and the + symbols, not sure why its like that to begin with. Also, assuming the numerical value in the 3rd column is column J so in this case it would be J4:

=(MAX(COUNTIF(H4:I4,"L")*1,1)*MAX(COUNTIF(H4:I4,"M")*3,1)*MAX(COUNTIF(H4:I4,"H")*5,1))*J4

The reason for the MAX's is so that the formula doesn't result in a 0 because there are no L's. 0 * the rest would result in a 0 regardless of what the rest is. Using Max forces it to be 1 * the rest if there are no L's (or M's or H's).

[DNAguy]

tigeravatar,

Thank you for the quick response. I copied the formula into my target cell (J4), and received a value of "0" after having an "H" in both the entry cells (H4 and I4); the value should display "25". I am attaching my spreadsheet for your reference.

DNAguy

Book1.xls

[CheshireCat]

How about:

=SUM((H4="L")*1,(H4="M")*3,(H4="H")*5)*SUM((I4="L")*1,(I4="M")*3,(I4="H")*5)

[DNAguy]

How about:

=SUM((H4="L")*1,(H4="M")*3,(H4="H")*5)*SUM((I4="L")*1,(I4="M")*3,(I4="H")*5)

CheshireCat,

Thank you for the assist. The formula worked great, just what I needed. I'm so glad I found this helpful forum.

DNAguy

---------- Post added at 03:12 PM ---------- Previous post was at 03:11 PM ----------

Can you clarify. Are you adding the letter values and then multiplying by the third value? For example if H4 = "M", I4 = "H" and J4 = 2 what's the result, is it (3+5)*2 = 16?

Are you doing this row by row or do you want a formula for the total for multiple rows?

A small example would be helpful......

daddylonglegs,

I attached my file to my response to tigeravatar. CheshireCat's formula worked great, but any other solutions are welcome so that I can continue to learn more about Excel.

DNAguy

[daddylonglegs]

I have two columns with the possible letter entries of "L", "M", and "H"; the numerical values for each will be 1, 3, and 5, respectively. I want to multiply the values in those two columns, with the numerical value displayed in a third column.

Can you clarify. Are you adding the letter values and then multiplying by the third value? For example if H4 = "M", I4 = "H" and J4 = 2 what's the result, is it (3+5)*2 = 16?

Are you doing this row by row or do you want a formula for the total for multiple rows?

A small example would be helpful......

[tigeravatar]

Alternate formulas:

=FIND(H4,"L M H")*(FIND(I4,"L M H"))

=PRODUCT(INDEX(FIND(H4:I4,"L M H"),))

But I'm confused on this:

I want to multiply the values in those two columns, with the numerical value displayed in a third column

I don't see a third column to multiply by at all.

I assumed the third column was column J that you wanted to multiply by, so my formula had a reference to J4. In the example workbook, you put the formula in cell J4 which caused a circular reference resulting in a value of 0.

Do you have a third column to multiply by?

[DNAguy]

tigeravatar,

Sorry about the confusion. What I meant to say was that I wanted the value from multiplying the H and I columns to be displayed in a third column, which would be J, which is where the formula would reside anyways. My bad.

DNAguy

[daddylonglegs]

OK, I think I misunderstood,

Another way would be to use this formula

=IF(H4="L",1,IF(H4="M",3,5))*IF(I4="L",1,IF(I4="M",3,5))

---------- Post added at 11:27 PM ---------- Previous post was at 11:24 PM ----------

But I'm confused on this......

I read it the same way, ta

I assume the third column is where the result should go......

[tigeravatar]

I assume the third column is where the result should go......

Ohhhh, ok. That makes sense then, lol.

[Cutter]

@ DNAguy

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.