How to SUM only numbers in a range with Text?

  • Thread starter Thread starter Guest
  • Start date Start date
=(C9-SUMPRODUCT(--(0&SUBSTITUTE(SUBSTITUTE(F9:AD9,"T ",""),"E ",""))))/2


will work as well


--


Regards,


Peo Sjoblom
 
Hi Martin,

Seeing my response in print it looks a bit terse - I must have been
associating with Harlan too much <g>

I did not mean to put your formula down, I was just in a bit of a rush when
I wrote it. Your formula does satisfy the OP's requirements as stated with
no double digit numbers given.

My apologies, it was not meant that way.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Hi Martin,
Seeing my response in print it looks a bit terse - I must have been
associating with Harlan too much <g>

I did not mean to put your formula down, I was just in a bit of a rush
when I wrote it. Your formula does satisfy the OP's requirements as
stated with no double digit numbers given.

My apologies, it was not meant that way.
Click to expand...


Aren't you supposed to be a "dour" Scot? <vbg>


--


Regards,


Peo Sjoblom
 
Peo Sjoblom said:
Aren't you supposed to be a "dour" Scot? <vbg>
Click to expand...


LOL

Incidentally does anyone know what has happened to Harlan lately?

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Sandy Mann said:
LOL

Incidentally does anyone know what has happened to Harlan lately?

--
Click to expand...


Vacation? Or maybe there is a convention for mathematically inclined
curmudgeons somewhere?
He was gone for some time and returned with a few posts but he seems to be
gone again, we have a saying in Sweden

"You don't miss the sow until the sty is empty"


Somehow he keeps other posters in line or they will be corrected <vbg>


--


Regards,


Peo Sjoblom
 
Bernie,

I found out that the "array enter a forumla" just puts the squirly brackets
around the formula. So when I do this, I still get the #VALUE! error. Any
ideas why?

I've looked at the Excel HELP and I've tried to rectify this error. If you
don't know why i'm getting the error, please just let me know so I'll quit
begging :)

Thanks!
 
David,

Thanks for trying. This forumla only works if every sell in the range of
F9:AD9 has something in it. Some cells are empty and so I get a value error.

This works if every cell is populated but we only see the client 1-3 times a
week.

Any other ideas?

Thanks!
 
Sandy,

You did it!! Thanks everyone for your help, especially you Sandy!

Best Regards,

Aundria
 
Bernie,

I'm not sure why Sandy's worked and yours did not, but I'm thankful to you
for getting me on the right track!

Aundria
 
Thanks for the thanks but it was Martin W's formula

How about that Martin? You won the race <g>

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Hi Sandy,

It's not about the race
It's about the journey <g>

I got as much from this thread as the OP.

P.S. Your earlier thread didn't sound terse or Harlanesque
to me. I'm an Aussie Sandy, you need to be trying very
hard to ruffle my feathers.

Thanks Again
Martin
 
Sandy,

Oh I was confused! Thanks Martin!!! I went back and put a YES for his POST
also. I hope this is what gives everyone a good rating.

You all are very helpful!

Aundria
 
You could solve that by replacing the =--RIGHT(F9) formula by
=IF(F9="","",--RIGHT(F9))
 
Back
Top