SUM() Cell That Contains String&Integer

[Dtex20]

Hi Guys,

I've been searching for a day or two now to find exactly what i want, but i can't seem to find it anywhere.

The problem i have there is a column with numbers and strings.

5
10D
51
Pax
40D
21

Now i want to add all the numbers with a "D" next to it, and ignore the integer cells and ignore the string cells. So the answer to the example above would be 50. 10D + 40D

Is there a way to do this in a formula, I've searched for similar things but nothing that meets the exact requirement.

Thanks for your help.

 

[Marcelo Branco]

Assuming the values in A2:A7, maybe...

Array formula
=SUM(IF(ISNUMBER(SEARCH("D",A2:A7)),IFERROR(--SUBSTITUTE(A2:A7,"D",""),0)))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[Special-K99]

=SUM(IF(ISNUMBER(SEARCH("D",A1:A6)),SUBSTITUTE(A1:A6,"D","")+0))
Array formula, use Ctrl-Shift-Enter

 

[Dtex20]

Thankyou so much guys, exactly what i need!

I would of never of thought to substitute the "D".

 

[Aladin Akyurek]

As a precaution...

Replace the bit

SUBSTITUTE(A1:A6,"D","")

with

SUBSTITUTE(UPPER(A1:A6),"D","")

 

[Marcelo Branco]

As a precaution...

Replace the bit

SUBSTITUTE(A1:A6,"D","")

with

SUBSTITUTE(UPPER(A1:A6),"D","")

Yes, good point - SUBSTITUTE is case sensitive.
Besides that it's important to wrap SUBSTITUTE with IFERROR(..,0) - in case there is something like DAX in the data.

M.