Can I make a word represent a certain number in Excel?

[bennyh82]

Hi there,

I'm new to the Forum and an Excel novice so I was hoping someone might be able to help with my query!

I'm interested to know if a word can represent a certain number in Excel? I have searched the forum for answers to similar questions and seen some responses regarding VLOOKUPS but I'm not sure if this is the right route to go down or how I would go about this anyway.

My worksheet contains data about nursery attendances and I ideally want the word 'Full Day' to represent a value (say, £20) and the word 'Half Day' to represent a value (say, £10). The table would look something like the below:

A B C D E F G
1 Child Name Monday Tuesday Wednesday Thursday Friday Weekly Spend
2 Andrew Full Day Full Day Full Day
3 Bethany Full Day Half Day Half Day
4 Chris Full Day Full Day Full Day Half Day

and I would like the 'Weekly Spend' column to calculate the weekly spend based on the values represented by the words 'Half Day' and 'Full Day' - so Andrew would be £60, Bethany would be £40, etc.

I would be really grateful if anyone could help me out with this - please just message me back if the question doesn't make sense and I'll try and include more detail/explain myself better! I'm using Excel 2016 on Windows, by the way.

Thanks!

Ben

 

[Fluff]

Hi & welcome to MrExcel.
Maybe
=COUNTIF(B2:F2,"Full Day")*20+COUNTIF(B2:F2,"Half Day")*10

 

[bennyh82]

Hi and thanks for the quick reply!

I've tried entering that formula into the 'Weekly Spend' column but it just comes out as '0'. Some days were initially blank and I thought this might be the problem but even when I make sure each day is filled with either 'Full Day' or 'Half Day' it still comes out as zero.

Please let me know if you have any other suggestions or a potential workaround to overcome this issue.

Thanks again,

Ben

 

[Fluff]

Make sure that you only have one space before the word Day & that there are no leading/trailing spaces. This is what I get

Excel 2013/2016
	A	B	C	D	E	F	G
1	Child	Monday	Tuesday	Wednesday	Thursday	Friday
2	a	Full Day	full day	half day			50
3	b	half day		Full Day	Full Day		50
4	c		Full day	Full Day	Half Day	Half day	60
5	d		Full day	Full Day	Full Day	Full Day	80
Sheet1

Cell Formulas
Range	Formula
G2	=COUNTIF(B2:F2,"Full Day")*20+COUNTIF(B2:F2,"Half Day")*10

 

[bennyh82]

Ah yes, it works perfectly now! Turns out there were trailing/leading spaces everywhere.

Thanks for taking the time to explain, I really appreciate your help.

Cheers,

Ben

 

[Fluff]

Glad to help & thanks for the feedback