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?
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email
"excelFan" wrote in message
...
"Bernard Liengme" wrote:
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
"excelFan" wrote in message
...
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
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