Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exclude text from sums
I have to put text into several cells along with numbers, mainly astericks
and some letters, but I also want to sum these cells. How do i sum the numbers and exclude the text? Please help!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exclude text from sums
Use SUM which ignores non-numeric data
"Be18" wrote: I have to put text into several cells along with numbers, mainly astericks and some letters, but I also want to sum these cells. How do i sum the numbers and exclude the text? Please help!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exclude text from sums
the simple way to do this is to put the numbers by themselves into a range
which you can add in the display range enter ="wanted text"&[cell with the number] "Be18" wrote: I have to put text into several cells along with numbers, mainly astericks and some letters, but I also want to sum these cells. How do i sum the numbers and exclude the text? Please help!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exclude text from sums
Thank you very much for your comment. However when i try this it excludes the
whole cell but i still need to include the number in that cell. I just want to exclude the asterisks or letter following the number! Thanks again "Toppers" wrote: Use SUM which ignores non-numeric data "Be18" wrote: I have to put text into several cells along with numbers, mainly astericks and some letters, but I also want to sum these cells. How do i sum the numbers and exclude the text? Please help!! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exclude text from sums
Just SUM() them, SUM() ignores text.
-- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Be18" wrote in message ... I have to put text into several cells along with numbers, mainly astericks and some letters, but I also want to sum these cells. How do i sum the numbers and exclude the text? Please help!! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exclude text from sums
Thank you for this. However when i do sum it seems to exclude the whole cell
not just the letter! "Sandy Mann" wrote: Just SUM() them, SUM() ignores text. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Be18" wrote in message ... I have to put text into several cells along with numbers, mainly astericks and some letters, but I also want to sum these cells. How do i sum the numbers and exclude the text? Please help!! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exclude text from sums
Add this to an adjacent column, copy down, then sum this columns
=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Be18" wrote in message ... Thank you very much for your comment. However when i try this it excludes the whole cell but i still need to include the number in that cell. I just want to exclude the asterisks or letter following the number! Thanks again "Toppers" wrote: Use SUM which ignores non-numeric data "Be18" wrote: I have to put text into several cells along with numbers, mainly astericks and some letters, but I also want to sum these cells. How do i sum the numbers and exclude the text? Please help!! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exclude text from sums
Hi Toppers,
Maybe there is a macro that exist already to clean up unwanted text or strings or whatever and leave the cell with only the wanted numbers for summing or averaging or whatever purpose...Ive seen more than a dozen of same questions in the forum... or maybe u can do it simply... regards again... -- ***** birds of the same feather flock together.. "Toppers" wrote: Use SUM which ignores non-numeric data "Be18" wrote: I have to put text into several cells along with numbers, mainly astericks and some letters, but I also want to sum these cells. How do i sum the numbers and exclude the text? Please help!! |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exclude text from sums
Bob,
Your formula returns text numbers which of course SUM() to zero. If I convert them to numbers by adding a double unary in front of the formula I get #VALUE! for all text cells. If I use your formula and try to add them up using a SUMPRODUCT() with a double unary in front I again get #VALUE! Is there any other way of adding them up? -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Bob Phillips" wrote in message ... Add this to an adjacent column, copy down, then sum this columns =MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Be18" wrote in message ... Thank you very much for your comment. However when i try this it excludes the whole cell but i still need to include the number in that cell. I just want to exclude the asterisks or letter following the number! Thanks again "Toppers" wrote: Use SUM which ignores non-numeric data "Be18" wrote: I have to put text into several cells along with numbers, mainly astericks and some letters, but I also want to sum these cells. How do i sum the numbers and exclude the text? Please help!! |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exclude text from sums
Two choices Sandy.
Either force it to a number at the start =--(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},""))))) or coerce in the sum =SUM(--F1:F5) as an array formula -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sandy Mann" wrote in message ... Bob, Your formula returns text numbers which of course SUM() to zero. If I convert them to numbers by adding a double unary in front of the formula I get #VALUE! for all text cells. If I use your formula and try to add them up using a SUMPRODUCT() with a double unary in front I again get #VALUE! Is there any other way of adding them up? -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Bob Phillips" wrote in message ... Add this to an adjacent column, copy down, then sum this columns =MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Be18" wrote in message ... Thank you very much for your comment. However when i try this it excludes the whole cell but i still need to include the number in that cell. I just want to exclude the asterisks or letter following the number! Thanks again "Toppers" wrote: Use SUM which ignores non-numeric data "Be18" wrote: I have to put text into several cells along with numbers, mainly astericks and some letters, but I also want to sum these cells. How do i sum the numbers and exclude the text? Please help!! |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exclude text from sums
Yes I tried both of them but as I said if there is a cell with just text and
no numbers the they both return #VALUE! But then perhaps the OP does not have text only cells Anyway, as you know it's late so I will sleepin it. -- Good night, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Bob Phillips" wrote in message ... Two choices Sandy. Either force it to a number at the start =--(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},""))))) or coerce in the sum =SUM(--F1:F5) as an array formula -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sandy Mann" wrote in message ... Bob, Your formula returns text numbers which of course SUM() to zero. If I convert them to numbers by adding a double unary in front of the formula I get #VALUE! for all text cells. If I use your formula and try to add them up using a SUMPRODUCT() with a double unary in front I again get #VALUE! Is there any other way of adding them up? -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Bob Phillips" wrote in message ... Add this to an adjacent column, copy down, then sum this columns =MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Be18" wrote in message ... Thank you very much for your comment. However when i try this it excludes the whole cell but i still need to include the number in that cell. I just want to exclude the asterisks or letter following the number! Thanks again "Toppers" wrote: Use SUM which ignores non-numeric data "Be18" wrote: I have to put text into several cells along with numbers, mainly astericks and some letters, but I also want to sum these cells. How do i sum the numbers and exclude the text? Please help!! |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exclude text from sums
=SUM(IF(NOT(ISERROR(F1:F5)),F1:F5))
array formula -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sandy Mann" wrote in message ... Yes I tried both of them but as I said if there is a cell with just text and no numbers the they both return #VALUE! But then perhaps the OP does not have text only cells Anyway, as you know it's late so I will sleepin it. -- Good night, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Bob Phillips" wrote in message ... Two choices Sandy. Either force it to a number at the start =--(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},""))))) or coerce in the sum =SUM(--F1:F5) as an array formula -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sandy Mann" wrote in message ... Bob, Your formula returns text numbers which of course SUM() to zero. If I convert them to numbers by adding a double unary in front of the formula I get #VALUE! for all text cells. If I use your formula and try to add them up using a SUMPRODUCT() with a double unary in front I again get #VALUE! Is there any other way of adding them up? -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Bob Phillips" wrote in message ... Add this to an adjacent column, copy down, then sum this columns =MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Be18" wrote in message ... Thank you very much for your comment. However when i try this it excludes the whole cell but i still need to include the number in that cell. I just want to exclude the asterisks or letter following the number! Thanks again "Toppers" wrote: Use SUM which ignores non-numeric data "Be18" wrote: I have to put text into several cells along with numbers, mainly astericks and some letters, but I also want to sum these cells. How do i sum the numbers and exclude the text? Please help!! |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exclude text from sums
Thank you Bob - I knew you could do it <g
-- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Bob Phillips" wrote in message ... =SUM(IF(NOT(ISERROR(F1:F5)),F1:F5)) array formula -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sandy Mann" wrote in message ... Yes I tried both of them but as I said if there is a cell with just text and no numbers the they both return #VALUE! But then perhaps the OP does not have text only cells Anyway, as you know it's late so I will sleepin it. -- Good night, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Bob Phillips" wrote in message ... Two choices Sandy. Either force it to a number at the start =--(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},""))))) or coerce in the sum =SUM(--F1:F5) as an array formula -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sandy Mann" wrote in message ... Bob, Your formula returns text numbers which of course SUM() to zero. If I convert them to numbers by adding a double unary in front of the formula I get #VALUE! for all text cells. If I use your formula and try to add them up using a SUMPRODUCT() with a double unary in front I again get #VALUE! Is there any other way of adding them up? -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Bob Phillips" wrote in message ... Add this to an adjacent column, copy down, then sum this columns =MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Be18" wrote in message ... Thank you very much for your comment. However when i try this it excludes the whole cell but i still need to include the number in that cell. I just want to exclude the asterisks or letter following the number! Thanks again "Toppers" wrote: Use SUM which ignores non-numeric data "Be18" wrote: I have to put text into several cells along with numbers, mainly astericks and some letters, but I also want to sum these cells. How do i sum the numbers and exclude the text? Please help!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exclude all but last text to the right | Excel Worksheet Functions | |||
How to add previous sums in a column to current sums in a column? | Excel Worksheet Functions | |||
Sums | Excel Discussion (Misc queries) | |||
Coverting string of text into individual sums? | Excel Discussion (Misc queries) | |||
Need a formula that sums a column but could exclude some cells | Excel Worksheet Functions |