Sum only data contain X

[Kishan]

Using Excel 2000

Hi,

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

Example data.

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

Thank you in advance

Regards,
Kishan

 

[Rick Rothstein]

Give this formula a try...

=IF(ISNUMBER(SEARCH("X",C6)),IF(C6="X|X|X","",SUM(0+MID(SUBSTITUTE(C6,"X",0),{1,3,5},1))),"")

 

[alansidman]

Here is a VBA solution

Option Explicit


Sub SumX()
    Dim lr As Long
    Dim s As Long
    Dim i As Long, y As Long
    lr = Range("C" & Rows.Count).End(xlUp).Row
    s = 0
    For i = 6 To lr
        If InStr(Range("C" & i), "X") > 0 Then    'X is case sensitive here.  I used Caps
            y = Range("C" & i).Offset(0, 1)
            s = s + y
        End If
    Next i
    MsgBox ("Sum of s = " & s)


End Sub

 

[gerald24]

Give this formula a try...

=IF(ISNUMBER(SEARCH("X",C6)),IF(C6="X|X|X","",SUM(0+MID(SUBSTITUTE(C6,"X",0),{1,3,5},1))),"")

Hi Rick, could you please explain your formula?

Thank you!!!

 

[Rick Rothstein]

Hi Rick, could you please explain your formula?

This, from the first IF...

ISNUMBER(SEARCH("X",C6))

tests whether C6 contains an X or not. If not, the formula output the empty text string "". If it does contain an X, then this gets executed...

F(C6="X|X|X","",SUM(0+MID(SUBSTITUTE(C6,"X",0),{1,3,5},1)))

The first test C6="X|X|X" looks to see if there are only X's in the cell... if yes, the empty text string is returned (as per the OP's request), otherwise this is executed...

SUM(0+MID(SUBSTITUTE(C6,"X",0),{1,3,5},1))

The first thing this part does ie substitute a zero in place of any X's, then it creates an array of numbers pulled from the first, third and fifth characters in the text (those correspond to the numbers between the vertical bar symbol), then 0 is added to each array element so that the SUM function has real numbers to add (it cannot add an array of text constants even if those text items look like numbers, so the text must be converted to real numbers).

 

[Kishan]

Here is a VBA solution

Option Explicit


Sub SumX()
    Dim lr As Long
    Dim s As Long
    Dim i As Long, y As Long
    lr = Range("C" & Rows.Count).End(xlUp).Row
    s = 0
    For i = 6 To lr
        If InStr(Range("C" & i), "X") > 0 Then    'X is case sensitive here.  I used Caps
            y = Range("C" & i).Offset(0, 1)
            s = s + y
        End If
    Next i
    MsgBox ("Sum of s = " & s)


End Sub

Hi alansidman, VBA is giving a message total sum of the range not individual, as I require.

Thank you for looking in it.

Kind Regards,
Kishan

 

[Kishan]

Give this formula a try...

=IF(ISNUMBER(SEARCH("X",C6)),IF(C6="X|X|X","",SUM(0+MID(SUBSTITUTE(C6,"X",0),{1,3,5},1))),"")

Brilliant!! Rick, Thank you for your help and time.

Appreciate it. Working as treat.

Regards,
Kishan