How to SUM only numbers in a range with Text?
Again, using Ctrl-Shift-Enter:
=(C9-SUM(IF(F9:AD9<"",VALUE(MID(F9:AD9,3,LEN(F9:AD9))) )))/2
Since you have a letter and a space, the text string is really two
characters long, so I changed the ,2, to ,3, in the MID function.
=(C9-SUM(IF(F9:AD9<"",VALUE(MID(F9:AD9,3,1)))))/2
would also work, since you only have a 2 or a 4 in the cell (a number string
one character long).
HTH,
Bernie
MS Excel MVP
"Aundria123" wrote in message
...
Bernie,
I hope you wont give up! I liked your formula and so I wanted to add that
when I click the "!" to see about the error, it looks to me that it gets
to
46/VALUE and breaks.
What does VALUE do? That seems to be the problem...
When I do EVALUATE FORMULA, it underlines and italicizes (#VALUE!<""
Then I click EVALUATE again and it underlines/italicizes
(IF( #VALUE!,VALUE(MID(F9:AD9,2,LEN(F9:AD9))))
I hit EVALUATE again
(46-#VALUE!)/2
I hit again
( #VALUE!)/2
I hit again
#VALUE!/2
I hit it for the last time and I only can RESTART
#VALUE!
Thanks for your help!
"Bernie Deitrick" wrote:
Aundria,
If you always have just one letter, then array enter (enter using
Ctrl-Shift-Enter) this formula
=(C9-SUM(IF(F9:AD9<"",VALUE(MID(F9:AD9,2,LEN(F9:AD9))) )))/2
HTH,
Bernie
MS Excel MVP
"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!
|