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
  #1  
Old September 18th, 2007, 05:46 PM posted to microsoft.public.excel.newusers
Aundria123
external usenet poster
 
Posts: 11
Default 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  
Old September 18th, 2007, 05:58 PM posted to microsoft.public.excel.newusers
jhyatt
external usenet poster
 
Posts: 20
Default 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  
Old September 18th, 2007, 06:10 PM posted to microsoft.public.excel.newusers
Bernie Deitrick
external usenet poster
 
Posts: 2,496
Default 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!



  #4  
Old September 18th, 2007, 06:24 PM posted to microsoft.public.excel.newusers
Peo Sjoblom
external usenet poster
 
Posts: 3,232
Default How to SUM only numbers in a range with Text?

Tell him he is an idiot! Use an extra column for any text entries


--


Regards,


Peo Sjoblom




"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!



  #5  
Old September 18th, 2007, 06:32 PM 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?

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!



  #6  
Old September 18th, 2007, 06:42 PM posted to microsoft.public.excel.newusers
Aundria123
external usenet poster
 
Posts: 11
Default 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!

  #7  
Old September 18th, 2007, 06:48 PM posted to microsoft.public.excel.newusers
Aundria123
external usenet poster
 
Posts: 11
Default 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  
Old September 18th, 2007, 07:38 PM posted to microsoft.public.excel.newusers
jhyatt
external usenet poster
 
Posts: 20
Default 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!

  #9  
Old September 18th, 2007, 08:36 PM posted to microsoft.public.excel.newusers
Mankind
external usenet poster
 
Posts: 12
Default How to SUM only numbers in a range with Text?

Peo,

nice joke !
I suggest that its better to *firstly* persuade her boss to use the extra
column, otherwise if *the bossy symptoms persist*, i suggest that she ask her
boss to do it himself.

we're excellers to reduce idiots g!

"Peo Sjoblom" wrote:

Tell him he is an idiot! Use an extra column for any text entries


--


Regards,


Peo Sjoblom




"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  
Old September 18th, 2007, 08:46 PM posted to microsoft.public.excel.newusers
Aundria123
external usenet poster
 
Posts: 11
Default How to SUM only numbers in a range with Text?

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 03:05 AM.


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