sum cells with characters

[mucah!t]

Hello all,

I'd like to sum all the numbers of cells proceeded by a "C"
In below example this would be cells M36 and N32 = 6
Any ideas?

Excel Workbook
	L	M	N
32	DF	V	C4
33	TR	2.5	V
34	V	IU	DF
35	4	TR	DF
36	V	C2	DF
Sheet1

Excel 2003

 

[ogo]

May be this can give you an idea

Sub test()
Sum = 0
'Currenty this code works upto Cells(50,50). The range can be changed
For i = 1 To 50
    For j = 1 To 50
        If Cells(i, j).Value Like "C*" Then
            Sum = Sum + Right(Cells(i, j).Value, 1)
        End If
    Next
Next
MsgBox Sum
End Sub

Thanks,
Ogo

 

[jasonb75]

Maybe

=SUM(IF(LEFT($L$32:$N$36,1)="C",--SUBSTITUTE($L$32:$N$36,"C","")))

Array confirmed with Shift Ctrl Enter.

 

[njimack]

=SUM(IF(LEFT(L32:N36)="C",SUBSTITUTE(L32:N36,"C","")+0))
confirmed with ctrl+shift+enter

 

[JackDanIce]

Perhaps not as clean as Jason's suggestion, but based on using array formula's, try:

=SUM(IF(LEFT(L32:L36,1)="C",RIGHT(L32:L36,LEN(L32:L36)-1)+0,0))

Again, type the above in but press CTRL+SHIFT+ENTER (not just ENTER)

 

[mucah!t]

Thanks guys,

JackDanice formula worked, the others give a value-error.
The only problem with JackDanice's formula is that when there's an "C" without a number in the range it gives an value-error.

Is there a solution for this?
Even better still would be if all occurances of a C [without a number] are counted as 8.

 

[jasonb75]

All three suggestions work for me with your sample table.

Array entered again, try

=SUM(IF(LEFT($L$32:$N$36,1)="C",--SUBSTITUTE($L$32:$N$36,"C",IF(LEN($L$32:$N$36)=1,8,""))))

How does that compare?

 

[mucah!t]

Awesome Jason!
Works great