Numerical value based on the contents of another cell

[mbtaichi]

Hi,
Is it possible to give a numerical value based on the contents of another cell?

In cell A1 would be any combination of the letters representing the days of the week;

MTUWTHFSA

For example, M would equal 2, TU would equal 3, W would equal 5, TH would equal 10, F would equal 20, and SA would equal 25 therefore I have all days of the week represented the total in cell B1 would be 65.

If A1= TUW B1 would = 7 or if A1 = FSA B1 would = 45

And any other combination thereafter.

Is there a formula to do this? if not is there any other way that I can do this?

I hope I have explained myself clearly and thank for any help given.

 

[strooman]

In B2:
International version: (with comma list separator)

=SUM(COUNTIF($A2,"*"&{"M","TU","W","TH","F","SA"}&"*")*{2,3,5,10,20,25})

Dutch Version (with backslash list separator)

=SUM(COUNTIF($A2;"*"&{"M"\"TU"\"W"\"TH"\"F"\"SA"}&"*")*{2\3\5\10\20\25})

and copy down

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][/tr][tr][td]

1​

[/td][td="bgcolor:#0070C0"]Days[/td][td="bgcolor:#0070C0"]Total[/td][/tr]
[tr][td]

2​

[/td][td]TUW[/td][td="bgcolor:#FFFF00"]

8​

[/td][/tr]
[tr][td]

3​

[/td][td]MTHF[/td][td="bgcolor:#FFFF00"]

32​

[/td][/tr]
[tr][td]

4​

[/td][td]TUWTH[/td][td="bgcolor:#FFFF00"]

18​

[/td][/tr]
[tr][td]

5​

[/td][td]MSA[/td][td="bgcolor:#FFFF00"]

27​

[/td][/tr]
[tr][td]

6​

[/td][td]MWTHSA[/td][td="bgcolor:#FFFF00"]

42​

[/td][/tr]
[tr][td]

7​

[/td][td]WTHFSA[/td][td="bgcolor:#FFFF00"]

60​

[/td][/tr]
[tr][td]

8​

[/td][td]THFSA[/td][td="bgcolor:#FFFF00"]

55​

[/td][/tr]
[tr][td]

9​

[/td][td]MWSA[/td][td="bgcolor:#FFFF00"]

32​

[/td][/tr]
[/table]

 

[mbtaichi]

In B2:
International version: (with comma list separator)

=SUM(COUNTIF($A2,"*"&{"M","TU","W","TH","F","SA"}&"*")*{2,3,5,10,20,25})

Dutch Version (with backslash list separator)

=SUM(COUNTIF($A2;"*"&{"M"\"TU"\"W"\"TH"\"F"\"SA"}&"*")*{2\3\5\10\20\25})

and copy down

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD="bgcolor: #0070C0"]Days[/TD]
[TD="bgcolor: #0070C0"]Total[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]TUW[/TD]
[TD="bgcolor: #FFFF00"]

8​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]MTHF[/TD]
[TD="bgcolor: #FFFF00"]

32​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]TUWTH[/TD]
[TD="bgcolor: #FFFF00"]

18​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]MSA[/TD]
[TD="bgcolor: #FFFF00"]

27​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]MWTHSA[/TD]
[TD="bgcolor: #FFFF00"]

42​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]WTHFSA[/TD]
[TD="bgcolor: #FFFF00"]

60​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]THFSA[/TD]
[TD="bgcolor: #FFFF00"]

55​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]MWSA[/TD]
[TD="bgcolor: #FFFF00"]

32​

[/TD]
[/TR]
</tbody>[/TABLE]

Thank you

 

[strooman]

You're welcome