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. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
How to SUM only numbers in a range with Text?
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! |
#2
|
|||
|
|||
How to SUM only numbers in a range with Text?
="t " &text(C9-(SUM(F9:AD9))/2,"####")
"Aundria123" wrote: 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! |
#3
|
|||
|
|||
How to SUM only numbers in a range with Text?
Hello,
Thank you for your reply. When I enter this, it just puts the letter t in front of my UNUSED UNITs #, i.e. t 41. But I need to be able to enter either t or e throughout the whole row. And I don't need the letter to be in the UNUSED UNITs column, only the number. I hope this makes sense. Thanks! "jhyatt" wrote: ="t " &text(C9-(SUM(F9:AD9))/2,"####") "Aundria123" wrote: 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! |
#4
|
|||
|
|||
How to SUM only numbers in a range with Text?
you could do this the only problem i have come up with is if any of the cells
f9:ad9 are blank it = an ERROR =C9-MID(F9,3,6)-MID(G9,3,6)-MID(H9,3,6)-MID(I9,3,6)-MID(J9,3,6)-MID(K9,3,6)-MID(L9,3,6)-MID(M9,3,6)-MID(N9,3,6)-MID(O9,3,6)-MID(P9,3,6)-MID(Q9,3,6)-MID(R9,3,6)-MID(S9,3,6)-MID(T9,3,6)-MID(U9,3,6)-MID(V9,3,6)-MID(V9,3,6)-MID(X9,3,6)-MID(Y9,3,6)-MID(Z9,3,6)-MID(AA9,3,6)-MID(AB9,3,6)-MID(AC9,3,6)-MID(AD9,3,6) "Aundria123" wrote: Hello, Thank you for your reply. When I enter this, it just puts the letter t in front of my UNUSED UNITs #, i.e. t 41. But I need to be able to enter either t or e throughout the whole row. And I don't need the letter to be in the UNUSED UNITs column, only the number. I hope this makes sense. Thanks! "jhyatt" wrote: ="t " &text(C9-(SUM(F9:AD9))/2,"####") "Aundria123" wrote: 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! |
#5
|
|||
|
|||
How to SUM only numbers in a range with Text?
Hey,
Thanks for trying. Some cells will be blank as we only enter a value when a client has been seen. F9 - AD9 are the days of the month and I enter "T 4" only when the client is seen. Thanks! "jhyatt" wrote: you could do this the only problem i have come up with is if any of the cells f9:ad9 are blank it = an ERROR =C9-MID(F9,3,6)-MID(G9,3,6)-MID(H9,3,6)-MID(I9,3,6)-MID(J9,3,6)-MID(K9,3,6)-MID(L9,3,6)-MID(M9,3,6)-MID(N9,3,6)-MID(O9,3,6)-MID(P9,3,6)-MID(Q9,3,6)-MID(R9,3,6)-MID(S9,3,6)-MID(T9,3,6)-MID(U9,3,6)-MID(V9,3,6)-MID(V9,3,6)-MID(X9,3,6)-MID(Y9,3,6)-MID(Z9,3,6)-MID(AA9,3,6)-MID(AB9,3,6)-MID(AC9,3,6)-MID(AD9,3,6) "Aundria123" wrote: Hello, Thank you for your reply. When I enter this, it just puts the letter t in front of my UNUSED UNITs #, i.e. t 41. But I need to be able to enter either t or e throughout the whole row. And I don't need the letter to be in the UNUSED UNITs column, only the number. I hope this makes sense. Thanks! "jhyatt" wrote: ="t " &text(C9-(SUM(F9:AD9))/2,"####") "Aundria123" wrote: 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! |
#6
|
|||
|
|||
How to SUM only numbers in a range with Text?
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! |
#7
|
|||
|
|||
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! |
#8
|
|||
|
|||
How to SUM only numbers in a range with Text?
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! |
#9
|
|||
|
|||
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! |
#10
|
|||
|
|||
How to SUM only numbers in a range with Text?
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! "Bernie Deitrick" wrote: 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! |
Thread Tools | |
Display Modes | |
|
|