Is there a simple way to sum a row of cells that have leading text?-CodePudding

I have 3 cells - they contain these 3 values beginning with 'x' with a sum of 15 that I have input manually for now.

Is there an easy way to sum the 1 4 10 to get the 15, in the 4th column?

I can cut the x off the beginning with this formula:

How do I cut all the x's off A1:C1, and sum the values in cell D1? I can only use cell D1 for my total. I have tried this:

Note: The leading text is always the same! Note: This is just a minimal reproducible example. My real data has 100 columns.

Many thanks

UPDATE:

here is a snip of the answer! not: all cells in range must be populated or will return an error.

CodePudding user response：

I would go with the following:

=RIGHT(A1,LEN(A1)-1) RIGHT(B1,LEN(B1)-1) RIGHT(C1,LEN(C1)-1)

Or:

SUBSTITUTE(A1,"x","") SUBSTITUTE(B1,"x","") SUBSTITUTE(C1,"x","")

Edit: JvdV has a really good suggestion of:

=SUMPRODUCT(--SUBSTITUTE(A1:C1,"x",""))

which sorts the OP's issue neatly.

To deal with empty cells, try the following:

=SUMPRODUCT(IFERROR(--SUBSTITUTE(A1:D1,"x",""),0))

CodePudding user response：

If the length of the leading text is fixed (1 in this case):

=SUM(VALUE(RIGHT(A1:C1,LEN(A1:C1)-1)))

then hit Ctrl Shift Enter instead of Enter