How to count the number of values in a range in a single cell

[kpp17]

Hi all! I am having a hard time figuring this out. I work in ticketing and have a sheet that is showing me all the seating in a venue by section. In one cell it has a range of numbers representing how many seats are on a specific row (ex. 1-10). I would like to create a column next to it showing the total number of seats on that row, so I can then easily add them together for a total. Some rows do now begin with seat 1. Any ideas?

 

[Snakehips]

@kpp17 Welcome
Maybe like below?

Book1
	A	B	C	D	E
1
2	Level	Section	Row	Seats	Qty
3	North	21	1	1,11	11
4	North	21	2	1,12	12
5	North	21	3	5,21	17
6	North	21	4	1,13	13
7	North	21	5	1,14	14
8	North	21	6	1,14	14
9	North	21	7	1,5	5
Sheet1

Cell Formulas
Range	Formula
E3:E9	E3	=RIGHT(SUBSTITUTE(D3,","," "),4)-LEFT(SUBSTITUTE(D3,","," "),4)+1

 

[kpp17]

@kpp17 Welcome
Maybe like below?

Book1
	A	B	C	D	E
1
2	Level	Section	Row	Seats	Qty
3	North	21	1	1,11	11
4	North	21	2	1,12	12
5	North	21	3	5,21	17
6	North	21	4	1,13	13
7	North	21	5	1,14	14
8	North	21	6	1,14	14
9	North	21	7	1,5	5
Sheet1

Cell Formulas
Range	Formula
E3:E9	E3	=RIGHT(SUBSTITUTE(D3,","," "),4)-LEFT(SUBSTITUTE(D3,","," "),4)+1

Exaclty like that! Thank you! I am very new to excel and could not figure it out.

 

[jtakw]

Hi,

Another way:

Book3.xlsx
	A	B	C	D	E
1
2	Level	Section	Row	Seats	Qty
3	North	21	1	1,11	11
4	North	21	2	1,12	12
5	North	21	3	5,21	17
6	North	21	4	1,13	13
7	North	21	5	1,14	14
8	North	21	6	1,14	14
9	North	21	7	1,5	5
Sheet1076

Cell Formulas
Range	Formula
E3:E9	E3	=RIGHT(D3,LEN(D3)-FIND(",",D3))-LEFT(D3,FIND(",",D3)-1)+1

 

[kpp17]

Hi,

Another way:

Book3.xlsx
	A	B	C	D	E
1
2	Level	Section	Row	Seats	Qty
3	North	21	1	1,11	11
4	North	21	2	1,12	12
5	North	21	3	5,21	17
6	North	21	4	1,13	13
7	North	21	5	1,14	14
8	North	21	6	1,14	14
9	North	21	7	1,5	5
Sheet1076

Cell Formulas
Range	Formula
E3:E9	E3	=RIGHT(D3,LEN(D3)-FIND(",",D3))-LEFT(D3,FIND(",",D3)-1)+1

Thank you for taking the time to look at this! I'm learning so much seeing how other people approach functions.

 

[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’)