Sum cells with numbers and text

[tabbytomo]

Hi everyone, I'm working on a shift planner that will include cells with numbers and text. I want to sum the numbers of these cells that have numbers and text.

My shift patterns will follow a strict criteria. Where X is a number:
Example 1: X Standard
Example 2: X Standard X Lower
Example 3: X Standard X Lower X Upper

I have Q2:S2 summing the contents of I2:O2, however my formula does not work for the second or third example and as I say I don't yet know enough to make a formula that works for that.

The formula I'm using in Q2:S2 is:

=SUM(IF(ISNUMBER(FIND(U1,$I$2:$O$2)),VALUE(LEFT($I$2:$O$2,FIND(U1,$I$2:$O$2)-1)),0))

U2:U4 is my list of words, Standard, Lower and Upper.

Any help greatly appreciated!

 

[bobsan42]

Place the text from U1:U3 in Q1, R1, S1 and try this in Q2 and spread it around:

=SUM(IFERROR(VALUE(MID($I2:$O2,IFERROR(SEARCH("?? "&Q$1,$I2:$O2,1),SEARCH("? "&Q$1,$I2:$O2,1)),2)),0))

2024-06-11 Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday		Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday		Standard	Lower	Upper
2	0800-2000	0700-1900							8 Standard 2 Lower 2 Upper	12 Standard							20	2	2
3									8 Standard 2 Lower 4 Upper	12 Lower							8	14	4
4									12 Lower 10 Standard	5 Upper							10	12	5
5																	0	0	0
6																	0	0	0
7																	0	0	0
Sheet1

Cell Formulas
Range	Formula
Q2:S7	Q2	=SUM(IFERROR(VALUE(MID($I2:$O2,IFERROR(SEARCH("?? "&Q$1,$I2:$O2,1),SEARCH("? "&Q$1,$I2:$O2,1)),2)),0))

A remark: this will only work properly with 1 or 2 digit numbers

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)