Character count, multiplication and based on cell value

[Manoj K]

First of all I am sorry, there were some mistake in my question submitted earlier.

I have a file containing 14 columns. I would like to get answers i.e. number of characters without space from column 2 (description) multiplied with quantity in column 6 either in column 7, or 8, or 9 or ….. 13 based on value in column 5. Example “if value in column 5 is 3, then the character count with quantity multiplied from column 6 should come in column 9” like if value in column 5 is 8, then the character count with quantity multiplied from column 6 should come in column 13”, etc, etc.. Please help.

 

[Special-K99]

You haven't displayed any row numbers so I'm having to guess what the rows are.
Assuming "AIR LINE" is on row 4

INDEX(G4:N4,1,E4)

should return the value to multiply the character count by

 

[Manoj K]

i want count numbers of charactors in column No. 2 for each row and result in column No. 7, 8, 9, 10,11,12 or 13 based on value in column 5

 

[Special-K99]

In that case why is K value 42? I don't see 42 characters in "| CT WATER OUTLET"
and L value should be 26 not 24
M value shoudn't be 42 either.

 

[Manoj K]

In that case why is K value 42? I don't see 42 characters in "| CT WATER OUTLET"
and L value should be 26 not 24
M value shoudn't be 42 either.

it is mannual counting error, that is why I am looking for some formula

 

[Special-K99]

This assumes data starts on row 4, adjust formula as necessary
In G
=IF(COLUMN()-6=$E4,(LEN($C4)-(LEN($C4)-LEN(SUBSTITUTE($C4," ","")))),"")

copy to the right and down for as many rows is needed

 

[Manoj K]

This assumes data starts on row 4, adjust formula as necessary
In G
=IF(COLUMN()-6=$E4,(LEN($C4)-(LEN($C4)-LEN(SUBSTITUTE($C4," ","")))),"")

copy to the right and down for as many rows is needed

not getting the result

 

[Scott Huish]

Except for some of the capitalization I have recreated your sheet, which helps us better than a picture by the way.

charlen.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	1	2	3	4	5	6	7	8	9	10	11	12	13	14
2	S. No.	Description	B/G		Inch	Qty	1"	2"	3"	4"	5"	6"	8"	B/G
3	1	↓Air Line			2	1		8
4	2	Filter ML Tank	15	20	3	1			12					900
5	3	↓CT Nwater Inlet	10	20	5	3					42			150
6	4	↑CT Water Out let	25	2	8	3							42	150
7	5	T-LOL CAP 5.0 KL KNOCK OUT TANK	26	16	6	1						24		416
8	6	↓PROCESS WATER LINE	26	5	4	1				17				130
Sheet1

What are you expecting exactly, and where and why?

 

[Scott Huish]

You're saying you're not getting the result, but I have to guess that it's not that you are not getting a result, but not the result you want.

Is this what you want?

charlen.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	1	2	3	4	5	6	7	8	9	10	11	12	13	14
2	S. No.	Description	B/G		Inch	Qty	1"	2"	3"	4"	5"	6"	8"	B/G
3	1	↓Air Line			2	1		8
4	2	Filter ML Tank	15	20	3	1			12					900
5	3	↓CT Nwater Inlet	10	20	5	3					42			150
6	4	↑CT Water Out let	25	2	8	3							42	150
7	5	T-LOL CAP 5.0 KL KNOCK OUT TANK	26	16	6	1						25		416
8	6	↓PROCESS WATER LINE	26	5	4	1				17				130
Sheet1

Cell Formulas
Range	Formula
G3:M8	G3	=IF($E3&""""=G$2,LEN(SUBSTITUTE($B3," ",""))*$F3,"")

 

[Manoj K]

You're saying you're not getting the result, but I have to guess that it's not that you are not getting a result, but not the result you want.

Is this what you want?

charlen.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	1	2	3	4	5	6	7	8	9	10	11	12	13	14
2	S. No.	Description	B/G		Inch	Qty	1"	2"	3"	4"	5"	6"	8"	B/G
3	1	↓Air Line			2	1		8
4	2	Filter ML Tank	15	20	3	1			12					900
5	3	↓CT Nwater Inlet	10	20	5	3					42			150
6	4	↑CT Water Out let	25	2	8	3							42	150
7	5	T-LOL CAP 5.0 KL KNOCK OUT TANK	26	16	6	1						25		416
8	6	↓PROCESS WATER LINE	26	5	4	1				17				130
Sheet1

Cell Formulas
Range	Formula
G3:M8	G3	=IF($E3&""""=G$2,LEN(SUBSTITUTE($B3," ",""))*$F3,"")

Yes, it worked well. Thanks