View Single Post
  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Héctor Miguel Héctor Miguel is offline
external usenet poster
 
Posts: 434
Default How to add (sum) a row cells with letter(s) aside or without n

hi, !

... This time just went perfectly with the new arrangement modified, as follow
=countif(c19:n19,"l")*7.5+sum(if(isnumber(--substitute(lower(c19:n19),"l",5)),--substitute(lower(c19:n19),"l",0)))
I'm expecting get better & set down ideas fluently for these kind of forum. TNX


i don't get why you are validating "isnumber(substitute(...,5))"
and (at the end) formula is using a different "substitute(...,0)"

but it's up to you (you are the man in the analysis stuff) :D
and... thanks for the feed-back ;)

regards,
hector.

__ OP __
[Q19] =countif(c19:n19,"l")*7.5+sum(if(isnumber(--substitute(lower(c19:n19),"l",0)),--substitute(lower(c19:n19),"l",0)))

or, from the beginning...

[Q3] =countif(c3:n3,"l")*7.5+sum(if(isnumber(--substitute(lower(c3:n3),"l",0)),--substitute(lower(c3:n3),"l",0)))

please, consider that only you have the TOTAL (possible) combinations of letters & numbers

as I said...
"please, revise and adapt/modify suggested array-formula in [R3] according other "expected" representation (letters & numbers)"
and...
"p.s. don't forget what you just learned in "Hints and Tips for New Posters" ;)"

regards,
hector.