adding #'s & #'s with letters

  • Thread starter Thread starter Chris
  • Start date Start date
C

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.
 
Possible way

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

The formula works for all stated values except "add".
Any thoughts on why that might be?
 
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","")))
 
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","")))
 
Back
Top