Thread
:
How to SUM only numbers in a range with Text?
View Single Post
#
32
September 19th, 2007, 09:42 PM posted to microsoft.public.excel.newusers
Aundria123
external usenet poster
Posts: 11
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!
Aundria123
View Public Profile
View message headers
Find all posts by Aundria123
Find all threads started by Aundria123