Sum only data with numbers

[Kishan]

Using Excel 2000

Hi,

I need to sum only data's values are in column C without "X" only numbers.

Example data.

Book1
	A	B	C	D
1
2
3
4			Data	Sum Only
5			Data	Numbers
6			X|2|1
7			2|1|2	5
8			1|X|1
9			1|1|2	4
10			1|1|X
11			2|1|X
12			2|2|2	6
13			X|1|2
14			X|X|X
15			X|X|2
16			1|1|1	3
17			1|X|2
18			2|X|1
19			2|X|2
20			2|2|2	6
21			2|1|X
22			2|1|2	5
23			2|X|2
24			1|X|X
25
Sheet1

Thank you in advance

Regards,
Kishan

 

[sheetspread]

Something like:

=sumproduct(--iserror(search("X",c6:c24)),d6:d24)

will work on your sample. Or are you trying to select strings with no alpha characters at all?

 

[jtakw]

Hi,

I "think" OP means per line:

Book1
	C	D
4	Data	Sum Only
5	Data	Numbers
6	X|2|1
7	2|1|2	5
8	1|X|1
9	1|1|2	4
10	1|1|X
11	2|1|X
12	2|2|2	6
13	X|1|2
14	X|X|X
15	X|X|2
16	1|1|1	3
17	1|X|2
18	2|X|1
19	2|X|2
20	2|2|2	6
21	2|1|X
22	2|1|2	5
23	2|X|2
24	1|X|X
Sheet72

Cell Formulas
Range	Formula
D6	=IFERROR(SUM(LEFT(C6,1),MID(C6,3,1),RIGHT(C6,1)),"")

Formula copied down.

 

[sheetspread]

sorry I totally missed that

 

[Kishan]

Hi,

I "think" OP means per line:

Sheet72​

Worksheet Formulas Cell Formula D6 =IFERROR(SUM(LEFT(C6,1),MID(C6,3,1),RIGHT(C6,1)),"") <tbody> </tbody>

<tbody>
</tbody>

Formula copied down.

Hi, jtakw, i am getting result #¿NAME?

Regards,
Kishan

 

[Rick Rothstein]

Worksheet Formulas Cell Formula D6 =IFERROR(SUM(LEFT(C6,1),MID(C6,3,1),RIGHT(C6,1)),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Why use the SUM function?

=IFERROR(LEFT(C6)+MID(C6,3,1)+RIGHT(C6),"")

Although if you did want to use the SUM function, maybe write it this way...

=IFERROR(SUM(0+MID(C6,{1,3,5},1)),"")

 

[Kishan]

Why use the SUM function?

=IFERROR(LEFT(C6)+MID(C6,3,1)+RIGHT(C6),"")

Although if you did want to use the SUM function, maybe write it this way...

=IFERROR(SUM(0+MID(C6,{1,3,5},1)),"")

Hi, Rick, unfortunately I am getting same result #¿NAME? I don't know what I am doing wrong?


Regards,
Kishan

 

[Rick Rothstein]

Hi, Rick, unfortunately I am getting same result #¿NAME? I don't know what I am doing wrong?

I forgot, you are using XL2000 and IFERROR does not exist in your version of Excel. Give this formula a try instead...

=IF(ISERROR(SUM(0+MID(C6,{1,3,5},1))),"",SUM(0+MID(C6,{1,3,5},1)))

 

[Kishan]

I forgot, you are using XL2000 and IFERROR does not exist in your version of Excel. Give this formula a try instead...

=IF(ISERROR(SUM(0+MID(C6,{1,3,5},1))),"",SUM(0+MID(C6,{1,3,5},1)))

Rick, Spot on it is resulting perfect!!

Thank you very much for you kind help and time!!

Kind Regards,
Kishan