How to SUM only numbers in a range with Text?
Bernie,
I'm not sure if I'm doing the CTRL+SHIFT+ENTER right. Can you dumb it down
more for me? When I try this, nothing gets entered into the cell.
Right now I do a COPY/PASTE of the formula into the CELL where UNUSED UNITs
are calculated. I see "####" indicating there is a error somewhere.
It will always be 1 letter and 1 number, i.e. T 4 OR E 2. There is a space
between the letter and the number.
Thank you 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!
|