Numerical value based on the contents of another cell

mbtaichi

Board Regular
Joined
Jan 5, 2016
Messages
71
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.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
In B2:
International version: (with comma list separator)
Code:
=SUM(COUNTIF($A2,"*"&{"M","TU","W","TH","F","SA"}&"*")*{2,3,5,10,20,25})

Dutch Version (with backslash list separator)
Code:
=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]
 
Upvote 0
In B2:
International version: (with comma list separator)
Code:
=SUM(COUNTIF($A2,"*"&{"M","TU","W","TH","F","SA"}&"*")*{2,3,5,10,20,25})

Dutch Version (with backslash list separator)
Code:
=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
 
Upvote 0

Forum statistics

Threads
1,226,693
Messages
6,192,463
Members
453,725
Latest member
cvsdatreas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top