Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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

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

X1 | X4 | X10

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

I can cut the x off the beginning with this formula:
enter image description here

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:
enter image description here

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!
enter image description here

>Solution :

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.

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading