# Calculate a Sum of all those cells that also contain some text strings

#### rajtak

##### Board Regular
Hi,

I want to calculate a Sum/Total of all those cells that also contain some text strings For example:

Cell A1 has
"ABC-2"

Cell B1 has
"DEF-3"

Cell C1 has
"XYZ-5"

So the total would be 10

But as this an example, it is pretty easy to calculate this manually but i have a long list of alphanumeric values. The good part is, these numbers are separated by a hyphen (-).

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying
Hi,

I want to calculate a Sum/Total of all those cells that also contain some text strings For example:

Cell A1 has
"ABC-2"

Cell B1 has
"DEF-3"

Cell C1 has
"XYZ-5"

So the total would be 10

But as this an example, it is pretty easy to calculate this manually but i have a long list of alphanumeric values. The good part is, these numbers are separated by a hyphen (-).

Hi,

If your values are same as "ABC-2" then you can use text to columns and sum those.

Arun C M

Hi,

Thanks for your suggestion, but i don't want this to be separated in other columns.

Hi,

Thanks for your suggestion, but i don't want this to be separated in other columns.

Hi,

If your values are keep on updating then you can use this forula on next column and make as sum
=RIGHT(J16,FIND("-",J16,1)-3)

Arun C M

Given your example data, this formula will sum the numeric values:
Code:
``=SUMPRODUCT(--RIGHT(A1:C1,LEN(A1:C1)-SEARCH("-",A1:C1)))``
It assumes that all of the referenced cells contain a hyphen and that the character text to the right of the hyphen can be coerced into a number.

Hope that helps...

Here's another way...

=SUMPRODUCT(REPLACE(A1:C1,1,FIND("-",A1:C1),"")+0)

Hope this helps!

Replies
2
Views
256
Replies
5
Views
170
Replies
1
Views
205
Replies
8
Views
547
Replies
2
Views
429

1,196,327
Messages
6,014,665
Members
441,834
Latest member
GHOSTOF309

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back