ExcelBanter - sum text & numbers

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - sum text & numbers (https://www.excelbanter.com/excel-discussion-misc-queries/238787-sum-text-numbers.html)

sum text & numbers

I have a spredsheet
A27 = V8
B27 = V12
C27 = V6
etc. etc.
I would like X27 to equal sum of numbers only , Letter is always "V"
so in this case X27 = 26

--
grizz

sum text & numbers

"grizzly6969" wrote:
I would like X27 to equal sum of numbers only , Letter is always "V"
so in this case X27 = 26

=SUMPRODUCT(--MID(A27:C27,2,10))

Works for numbers (after "V") up to 10 digits; increase as needed.

Works for one contiguous range. Add MID() terms as needed for additional
ranges, e.g.:

=SUMPRODUCT(MID(A27:C27,2,10)+MID(A28:C28,2,10)+MI D(D29,2,10))

Simply add MID terms if all cells are discontiguous, e.g.:

=MID(A27,2,10)+MID(B28,2,10)+MID(C29,2,10)

----- original message -----

"grizzly6969" wrote in message
...
I have a spredsheet
A27 = V8
B27 = V12
C27 = V6
etc. etc.
I would like X27 to equal sum of numbers only , Letter is always "V"
so in this case X27 = 26

--
grizz

sum text & numbers

On Mon, 3 Aug 2009 13:36:02 -0700, grizzly6969
wrote:

I have a spredsheet
A27 = V8
B27 = V12
C27 = V6
etc. etc.
I would like X27 to equal sum of numbers only , Letter is always "V"
so in this case X27 = 26

Try this formula:

=SUMPRODUCT(0+SUBSTITUTE(A27:C27,"V",""))

Hope this helps / Lars-Åke