sum numbers which contain characters

[excelFan]

in column A (A1:A11)
12
c8
22
c1
54
c19
c6
25
c3
c7
55
how to get the sum only for those number which begins with "c"?
many thanks for any help

 

[Ragdyer]

Try this:

=SUMPRODUCT((ISTEXT(A1:A11)*(RIGHT(A1:A11,LEN(A1:A11)-1))))

 

[Bernard Liengme]

If you meant 'count' how many values begin with letter c
=SUMPRODUCT(--(LEFT(A1:A11)="c"))
Answer 6

If you really do want to 'sum' the numbers following the letter c
=SUMPRODUCT(--(LEFT(A1:A11)="c"), --MID(A1:A11,2,256))
Answer 44

best wishes

 

[Jacob Skaria]

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

=SUM(--IF(LEFT(A1:A11,1)="c",MID(A1:A11,2,10)))



If this post helps click Yes

 

[excelFan]

If you meant 'count' how many values begin with letter c
=SUMPRODUCT(--(LEFT(A1:A11)="c"))
Answer 6

If you really do want to 'sum' the numbers following the letter c
=SUMPRODUCT(--(LEFT(A1:A11)="c"), --MID(A1:A11,2,256))
Answer 44

best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email




Thanks Barnard

your formula =SUMPRODUCT(--(LEFT(A1:A11)="c"), --MID(A1:A11,2,256))
works fine for the first time, but when editing any cell value returns an
error (#VALUE, and in case there is an empty cell in the range gives also an
error.Please help for solution.
regards

 

[Bernard Liengme]

Not sure I fully understand your problem
I agree that a blank cell will cause havoc
This solves that problem
=SUMPRODUCT(--(LEFT(A1:A11)="c"),--(MID(A1:A11,2,256)&0))/10

What other edits cause a problem?