Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding numbers or characters to existing numbers | Excel Worksheet Functions | |||
numbers to characters | Excel Worksheet Functions | |||
Deleting characters that are not numbers | Excel Discussion (Misc queries) | |||
characters around numbers | Excel Discussion (Misc queries) | |||
Converting characters to numbers | Excel Worksheet Functions |