adding #'s & #'s with letters

[Chris]

I am looking to do a sum function.

I would like to do a total sum. In a column I have values
like 3, 4A, 5N, off, add, drop, and some blank cells. I
would like to total all cells in the column with values
that have only numbers and numbers with a letter (3, 4A,
5N), leaving out blank cells,"add","off","drop".


Thanks.

 

[Peo Sjoblom]

Possible way

=SUMPRODUCT(--(0&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWE
R(A1:A10),"a",""),"n",""),"off",""),"drop",""),"add","")))

 

[Chris]

Peo,

The formula works for all stated values except "add".
Any thoughts on why that might be?

 

[Peo Sjoblom]

Probably because it replaces a in add before it replaces add, try to replace
add first like this

=SUMPRODUCT(--(0&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWE
R(A1:A10),"add",""),"n",""),"off",""),"drop",""),"a","")))

 

[J.E. McGimpsey]

The innermost substitute converts "add" to "dd", so the outermost
doesn't see "add". Try:

=SUMPRODUCT(--(0&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITU
TE(LOWER(A1:A10),"a",""),"n",""),"off",""),"drop",""),"dd","")))