A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How to SUM only numbers in a range with Text?



 
 
Thread Tools Display Modes
  #31  
Old September 19th, 2007, 09:38 PM posted to microsoft.public.excel.newusers
MartinW
external usenet poster
 
Posts: 848
Default ANSWERED How to SUM only numbers in a range with Text?

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 Mann" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"Aundria123" wrote in message
...
Sandy,

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

Best Regards,

Aundria

"Sandy Mann" wrote:

"MartinW" wrote in message
...
Can somebody else help out here?


As an academic exercise - (I think that Bernie's formulas is the way to
go) - this should handle blank cells or cells returning an empty string:

=(C9-(SUM(IF(F9:AD9"",RIGHT(F9:AD9)*1))/2))

--
HTH

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


Replace @mailinator.com with @tiscali.co.uk


"MartinW" wrote in message
...
Sorry that doesn't handle blank cells, although it will work if
you put a zero in the blank cells.

Can somebody else help out here?
I think an ISTEXT or maybe an IF(ISTEXT
can be worked into this array formula to handle the blanks
but I can't seem to get the syntax right.

The array formula I started with is
=(C9-(SUM(RIGHT(F9:AD9)*1)/2))

Regards
Martin


"MartinW" wrote in message
...
Hi Aundria,

Try this formula in AE9
=(C9-(SUM(RIGHT(F9:AD9)*1)/2))
This is an array formula and must be committed
with Ctrl+Shift+Enter and not just enter

HTH
Martin


"Aundria123" wrote in message
...
Hello,

I'm a new user to excel and forumlas. I have a column that has a
total
number of units in C9. In column C8 are names of people. I have to
either
put next to the names T 2, T 4, E 2 or E4 (F9 to AD9). I am using
the
following formula to subtract from the total number of units for
each
day in
the month.

Formula in Cell AE9: =(C9-(SUM(F9:AD9))/2)

This works perfectly for me when I only insert number 2 or 4. In
column
AE,it gives me the number of UNUSED UNITs.

Well now my boss wants me to enter the letter T or E with the
NUMBERS,
and I
don't know how to adjust this forumla to IGNORE the letter so that
the
formula continues to work. Right now when I add a letter to the
cell,
the
formula ignores the number.

I hope this makes sense as I'm desperate for a solution. I have
digged
through a bunch of help sites, but I'm not technical enough to
understand.

Thank you for your help!













  #32  
Old September 19th, 2007, 09:42 PM posted to microsoft.public.excel.newusers
Aundria123
external usenet poster
 
Posts: 11
Default ANSWERED How to SUM only numbers in a range with Text?

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

"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"Aundria123" wrote in message
...
Sandy,

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

Best Regards,

Aundria

"Sandy Mann" wrote:

"MartinW" wrote in message
...
Can somebody else help out here?


As an academic exercise - (I think that Bernie's formulas is the way to
go) - this should handle blank cells or cells returning an empty string:

=(C9-(SUM(IF(F9:AD9"",RIGHT(F9:AD9)*1))/2))

--
HTH

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


Replace @mailinator.com with @tiscali.co.uk


"MartinW" wrote in message
...
Sorry that doesn't handle blank cells, although it will work if
you put a zero in the blank cells.

Can somebody else help out here?
I think an ISTEXT or maybe an IF(ISTEXT
can be worked into this array formula to handle the blanks
but I can't seem to get the syntax right.

The array formula I started with is
=(C9-(SUM(RIGHT(F9:AD9)*1)/2))

Regards
Martin


"MartinW" wrote in message
...
Hi Aundria,

Try this formula in AE9
=(C9-(SUM(RIGHT(F9:AD9)*1)/2))
This is an array formula and must be committed
with Ctrl+Shift+Enter and not just enter

HTH
Martin


"Aundria123" wrote in message
...
Hello,

I'm a new user to excel and forumlas. I have a column that has a
total
number of units in C9. In column C8 are names of people. I have to
either
put next to the names T 2, T 4, E 2 or E4 (F9 to AD9). I am using
the
following formula to subtract from the total number of units for each
day in
the month.

Formula in Cell AE9: =(C9-(SUM(F9:AD9))/2)

This works perfectly for me when I only insert number 2 or 4. In
column
AE,it gives me the number of UNUSED UNITs.

Well now my boss wants me to enter the letter T or E with the
NUMBERS,
and I
don't know how to adjust this forumla to IGNORE the letter so that
the
formula continues to work. Right now when I add a letter to the
cell,
the
formula ignores the number.

I hope this makes sense as I'm desperate for a solution. I have
digged
through a bunch of help sites, but I'm not technical enough to
understand.

Thank you for your help!












  #33  
Old September 20th, 2007, 10:57 AM posted to microsoft.public.excel.newusers
David Biddulph
external usenet poster
 
Posts: 8,714
Default How to SUM only numbers in a range with Text?

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

"Aundria123" wrote in message
...
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?


"David Biddulph" wrote:

Sorry, I should have said =--RIGHT(F9), not =RIGHT(F9), as the latter
produces text, rather than a number. I apologise for causing you
confusion.
--
David Biddulph

"Aundria123" wrote in message
...
David,

I did what you suggested. I started with F42 to AD42, which
corresponds
with F9 to AD9. I have only numbers in my cell on the second page. I
then
put the below formula in AE9 (UNUSED UNITS column) but now the value is
always the same as in C9 (TOTAL UNITS). It's like the formula is no
long
doing the SUM / 2 part.

=(C9-(SUM(F42:AD42))/2)

What gives?

Thanks!

"David Biddulph" wrote:

One option is to produce a set of helper columns, so from "T 2" in F9
you
could extract the 2 by =RIGHT(F9), and copy across appropriately. Use
the
new columns in your SUM (and hide the new columns if you want to).
--
David Biddulph

"Aundria123" wrote in message
...
Hello,

I'm a new user to excel and forumlas. I have a column that has a
total
number of units in C9. In column C8 are names of people. I have to
either
put next to the names T 2, T 4, E 2 or E4 (F9 to AD9). I am using
the
following formula to subtract from the total number of units for
each
day
in
the month.

Formula in Cell AE9: =(C9-(SUM(F9:AD9))/2)

This works perfectly for me when I only insert number 2 or 4. In
column
AE,it gives me the number of UNUSED UNITs.

Well now my boss wants me to enter the letter T or E with the
NUMBERS,
and
I
don't know how to adjust this forumla to IGNORE the letter so that
the
formula continues to work. Right now when I add a letter to the
cell,
the
formula ignores the number.

I hope this makes sense as I'm desperate for a solution. I have
digged
through a bunch of help sites, but I'm not technical enough to
understand.

Thank you for your help!








 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:37 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.