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

[rajtak]

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 (-).

I need a excel formula/function to do this calculation. Please help me out.

 

[Arun C M]

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 (-).

I need a excel formula/function to do this calculation. Please help me out.

Hi,

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

Arun C M

 

[rajtak]

Hi,

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

 

[Arun C M]

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

 

[Colin Legg]

Given your example data, this formula will sum the numeric values:

=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...

 

[Domenic]

Here's another way...

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

Hope this helps!

 

[rajtak]

Hi Guys thanks for your reply functions worked perfectly