sum numbers which contain characters

  • Thread starter Thread starter excelFan
  • Start date Start date
E

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
 
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
 
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
 
Bernard Liengme said:
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
Click to expand...
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
 
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?
 
Back
Top