sum numbers which contain characters
 

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

sum numbers which contain characters
 

Try this:

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"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

sum numbers which contain characters
 

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

sum numbers which contain characters
 

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
---------------
Jacob Skaria


"excelFan" wrote:

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

sum numbers which contain characters
 

"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

sum numbers which contain characters
 

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