How to SUM only numbers in a range with Text?

[Peo Sjoblom]

=(C9-SUMPRODUCT(--(0&SUBSTITUTE(SUBSTITUTE(F9:AD9,"T ",""),"E ",""))))/2


will work as well


--


Regards,


Peo Sjoblom

 

[Sandy Mann]

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

 

[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.

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


--


Regards,


Peo Sjoblom

 

[Sandy Mann]

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

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

 

[Peo Sjoblom]

LOL

Incidentally does anyone know what has happened to Harlan lately?

--

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

 

[Guest]

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!

 

[Guest]

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!

 

[Guest]

Sandy,

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

Best Regards,

Aundria

 

[Guest]

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

 

[Sandy Mann]

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

 

[MartinW]

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

 

[Guest]

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

 

[David Biddulph]

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