| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif Fails w Date as Text
I have a column (A) that calculates a date to the nearest 1st of the month
based on a date in another column on another sheet (Sheet2!G). The calculation is of the form =VALUE(LEFT(Sheet2!G43,2))+1 & "/1" Column A is formatted as text. Then I have a set of cells (P1 to P8) that calculates the first of the month out 2 to 10 months. That calculation is of the form = (MONTH(TODAY())+#) & "/1" where # varies from 2 to 10. So in column A I get values like 10/1, 12/1, 11/1, etc. and in P I also get similar values. Column P is also formatted as text. In Column C there are work hours associated with the month starts in col A and I want to total the work hours for each month start, so in Column H If have the following calculations: H1: =SUMIF(A10:A100,P1,C10:C100) H2: =SUMIF(A10:A100,P2,C10:C100) etc. So when P2 = 10/1 I would expect H2 to contain the total of any cells in C that have a value of 10/1. Instead I get a zero. I have tried to confirm that the values match with the formula =IF(P2 = A5,1,0) where A5 displays 10/1 and the result is a 1, meaning that the cells match, but I still get only a 0 in H2. Any help appreciated. thanks ed |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif Fails w Date as Text
How is Column C formatted?
"expect_ed" wrote: I have a column (A) that calculates a date to the nearest 1st of the month based on a date in another column on another sheet (Sheet2!G). The calculation is of the form =VALUE(LEFT(Sheet2!G43,2))+1 & "/1" Column A is formatted as text. Then I have a set of cells (P1 to P8) that calculates the first of the month out 2 to 10 months. That calculation is of the form = (MONTH(TODAY())+#) & "/1" where # varies from 2 to 10. So in column A I get values like 10/1, 12/1, 11/1, etc. and in P I also get similar values. Column P is also formatted as text. In Column C there are work hours associated with the month starts in col A and I want to total the work hours for each month start, so in Column H If have the following calculations: H1: =SUMIF(A10:A100,P1,C10:C100) H2: =SUMIF(A10:A100,P2,C10:C100) etc. So when P2 = 10/1 I would expect H2 to contain the total of any cells in C that have a value of 10/1. Instead I get a zero. I have tried to confirm that the values match with the formula =IF(P2 = A5,1,0) where A5 displays 10/1 and the result is a 1, meaning that the cells match, but I still get only a 0 in H2. Any help appreciated. thanks ed |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif Fails w Date as Text
Col C is formatted as a number, with 2 decimal places.
"Toppers" wrote: How is Column C formatted? "expect_ed" wrote: I have a column (A) that calculates a date to the nearest 1st of the month based on a date in another column on another sheet (Sheet2!G). The calculation is of the form =VALUE(LEFT(Sheet2!G43,2))+1 & "/1" Column A is formatted as text. Then I have a set of cells (P1 to P8) that calculates the first of the month out 2 to 10 months. That calculation is of the form = (MONTH(TODAY())+#) & "/1" where # varies from 2 to 10. So in column A I get values like 10/1, 12/1, 11/1, etc. and in P I also get similar values. Column P is also formatted as text. In Column C there are work hours associated with the month starts in col A and I want to total the work hours for each month start, so in Column H If have the following calculations: H1: =SUMIF(A10:A100,P1,C10:C100) H2: =SUMIF(A10:A100,P2,C10:C100) etc. So when P2 = 10/1 I would expect H2 to contain the total of any cells in C that have a value of 10/1. Instead I get a zero. I have tried to confirm that the values match with the formula =IF(P2 = A5,1,0) where A5 displays 10/1 and the result is a 1, meaning that the cells match, but I still get only a 0 in H2. Any help appreciated. thanks ed |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif Fails w Date as Text
Hi Ed
I think you have the formula the wrong way round. You are saying the "dates" are in C and the values to be summed in A. Try =SUMIF(C10:C100,P1,A10:A100) -- Regards Roger Govier "expect_ed" wrote in message ... I have a column (A) that calculates a date to the nearest 1st of the month based on a date in another column on another sheet (Sheet2!G). The calculation is of the form =VALUE(LEFT(Sheet2!G43,2))+1 & "/1" Column A is formatted as text. Then I have a set of cells (P1 to P8) that calculates the first of the month out 2 to 10 months. That calculation is of the form = (MONTH(TODAY())+#) & "/1" where # varies from 2 to 10. So in column A I get values like 10/1, 12/1, 11/1, etc. and in P I also get similar values. Column P is also formatted as text. In Column C there are work hours associated with the month starts in col A and I want to total the work hours for each month start, so in Column H If have the following calculations: H1: =SUMIF(A10:A100,P1,C10:C100) H2: =SUMIF(A10:A100,P2,C10:C100) etc. So when P2 = 10/1 I would expect H2 to contain the total of any cells in C that have a value of 10/1. Instead I get a zero. I have tried to confirm that the values match with the formula =IF(P2 = A5,1,0) where A5 displays 10/1 and the result is a 1, meaning that the cells match, but I still get only a 0 in H2. Any help appreciated. thanks ed |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif Fails w Date as Text
I don't think that is correct. According to Excel the first parameter is the
compare range and the 3rd parameter is the cells to be summed. In any case I tried switching it and still get zero. Thanks anyway. ed "Roger Govier" wrote: Hi Ed I think you have the formula the wrong way round. You are saying the "dates" are in C and the values to be summed in A. Try =SUMIF(C10:C100,P1,A10:A100) -- Regards Roger Govier "expect_ed" wrote in message ... I have a column (A) that calculates a date to the nearest 1st of the month based on a date in another column on another sheet (Sheet2!G). The calculation is of the form =VALUE(LEFT(Sheet2!G43,2))+1 & "/1" Column A is formatted as text. Then I have a set of cells (P1 to P8) that calculates the first of the month out 2 to 10 months. That calculation is of the form = (MONTH(TODAY())+#) & "/1" where # varies from 2 to 10. So in column A I get values like 10/1, 12/1, 11/1, etc. and in P I also get similar values. Column P is also formatted as text. In Column C there are work hours associated with the month starts in col A and I want to total the work hours for each month start, so in Column H If have the following calculations: H1: =SUMIF(A10:A100,P1,C10:C100) H2: =SUMIF(A10:A100,P2,C10:C100) etc. So when P2 = 10/1 I would expect H2 to contain the total of any cells in C that have a value of 10/1. Instead I get a zero. I have tried to confirm that the values match with the formula =IF(P2 = A5,1,0) where A5 displays 10/1 and the result is a 1, meaning that the cells match, but I still get only a 0 in H2. Any help appreciated. thanks ed |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif Fails w Date as Text
Yes, I misread your posting.
Your dates are in A and the values to be summed are in C. It works absolutely fine for me. There must be something wrong with your data. Perhaps the values in column C are text, not numeric. That would give a result of 0. Try testing columns C with =ISNUMBER(C1) and copying down. -- Regards Roger Govier "expect_ed" wrote in message ... I don't think that is correct. According to Excel the first parameter is the compare range and the 3rd parameter is the cells to be summed. In any case I tried switching it and still get zero. Thanks anyway. ed "Roger Govier" wrote: Hi Ed I think you have the formula the wrong way round. You are saying the "dates" are in C and the values to be summed in A. Try =SUMIF(C10:C100,P1,A10:A100) -- Regards Roger Govier "expect_ed" wrote in message ... I have a column (A) that calculates a date to the nearest 1st of the month based on a date in another column on another sheet (Sheet2!G). The calculation is of the form =VALUE(LEFT(Sheet2!G43,2))+1 & "/1" Column A is formatted as text. Then I have a set of cells (P1 to P8) that calculates the first of the month out 2 to 10 months. That calculation is of the form = (MONTH(TODAY())+#) & "/1" where # varies from 2 to 10. So in column A I get values like 10/1, 12/1, 11/1, etc. and in P I also get similar values. Column P is also formatted as text. In Column C there are work hours associated with the month starts in col A and I want to total the work hours for each month start, so in Column H If have the following calculations: H1: =SUMIF(A10:A100,P1,C10:C100) H2: =SUMIF(A10:A100,P2,C10:C100) etc. So when P2 = 10/1 I would expect H2 to contain the total of any cells in C that have a value of 10/1. Instead I get a zero. I have tried to confirm that the values match with the formula =IF(P2 = A5,1,0) where A5 displays 10/1 and the result is a 1, meaning that the cells match, but I still get only a 0 in H2. Any help appreciated. thanks ed |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif Fails w Date as Text
Yes, that seems to be the problem. Even when I set the format to numeric the
cells remain as non-numeric. If I try =value(c10) I get a #Value error. Any suggestions for forcing the cells to numbers??? thanks for your help. ed "Roger Govier" wrote: Yes, I misread your posting. Your dates are in A and the values to be summed are in C. It works absolutely fine for me. There must be something wrong with your data. Perhaps the values in column C are text, not numeric. That would give a result of 0. Try testing columns C with =ISNUMBER(C1) and copying down. -- Regards Roger Govier "expect_ed" wrote in message ... I don't think that is correct. According to Excel the first parameter is the compare range and the 3rd parameter is the cells to be summed. In any case I tried switching it and still get zero. Thanks anyway. ed "Roger Govier" wrote: Hi Ed I think you have the formula the wrong way round. You are saying the "dates" are in C and the values to be summed in A. Try =SUMIF(C10:C100,P1,A10:A100) -- Regards Roger Govier "expect_ed" wrote in message ... I have a column (A) that calculates a date to the nearest 1st of the month based on a date in another column on another sheet (Sheet2!G). The calculation is of the form =VALUE(LEFT(Sheet2!G43,2))+1 & "/1" Column A is formatted as text. Then I have a set of cells (P1 to P8) that calculates the first of the month out 2 to 10 months. That calculation is of the form = (MONTH(TODAY())+#) & "/1" where # varies from 2 to 10. So in column A I get values like 10/1, 12/1, 11/1, etc. and in P I also get similar values. Column P is also formatted as text. In Column C there are work hours associated with the month starts in col A and I want to total the work hours for each month start, so in Column H If have the following calculations: H1: =SUMIF(A10:A100,P1,C10:C100) H2: =SUMIF(A10:A100,P2,C10:C100) etc. So when P2 = 10/1 I would expect H2 to contain the total of any cells in C that have a value of 10/1. Instead I get a zero. I have tried to confirm that the values match with the formula =IF(P2 = A5,1,0) where A5 displays 10/1 and the result is a 1, meaning that the cells match, but I still get only a 0 in H2. Any help appreciated. thanks ed |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif Fails w Date as Text
Hi I
in a blank cell on your sheet enter 1 Copy that cell Mark the range of your "non numeric data" Paste SpecialMultiply -- Regards Roger Govier "expect_ed" wrote in message ... Yes, that seems to be the problem. Even when I set the format to numeric the cells remain as non-numeric. If I try =value(c10) I get a #Value error. Any suggestions for forcing the cells to numbers??? thanks for your help. ed "Roger Govier" wrote: Yes, I misread your posting. Your dates are in A and the values to be summed are in C. It works absolutely fine for me. There must be something wrong with your data. Perhaps the values in column C are text, not numeric. That would give a result of 0. Try testing columns C with =ISNUMBER(C1) and copying down. -- Regards Roger Govier "expect_ed" wrote in message ... I don't think that is correct. According to Excel the first parameter is the compare range and the 3rd parameter is the cells to be summed. In any case I tried switching it and still get zero. Thanks anyway. ed "Roger Govier" wrote: Hi Ed I think you have the formula the wrong way round. You are saying the "dates" are in C and the values to be summed in A. Try =SUMIF(C10:C100,P1,A10:A100) -- Regards Roger Govier "expect_ed" wrote in message ... I have a column (A) that calculates a date to the nearest 1st of the month based on a date in another column on another sheet (Sheet2!G). The calculation is of the form =VALUE(LEFT(Sheet2!G43,2))+1 & "/1" Column A is formatted as text. Then I have a set of cells (P1 to P8) that calculates the first of the month out 2 to 10 months. That calculation is of the form = (MONTH(TODAY())+#) & "/1" where # varies from 2 to 10. So in column A I get values like 10/1, 12/1, 11/1, etc. and in P I also get similar values. Column P is also formatted as text. In Column C there are work hours associated with the month starts in col A and I want to total the work hours for each month start, so in Column H If have the following calculations: H1: =SUMIF(A10:A100,P1,C10:C100) H2: =SUMIF(A10:A100,P2,C10:C100) etc. So when P2 = 10/1 I would expect H2 to contain the total of any cells in C that have a value of 10/1. Instead I get a zero. I have tried to confirm that the values match with the formula =IF(P2 = A5,1,0) where A5 displays 10/1 and the result is a 1, meaning that the cells match, but I still get only a 0 in H2. Any help appreciated. thanks ed |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif Fails w Date as Text
Well, I actually simplified that col a bit to shorten the description.
The cells in row C actually contain a formula: =IF(Sheet2!M37="",Sheet2!L37,Sheet2!M37) because I am pasting a worksheet into Sheet 2 that will have work hr values either in col L (original) or col M (updated). So when I use the paste special multiply everything changes to #VALUE errors?? The pasted cells in sheet2 come in as General formatted. Thanks again for your help. ed "Roger Govier" wrote: Hi I in a blank cell on your sheet enter 1 Copy that cell Mark the range of your "non numeric data" Paste SpecialMultiply -- Regards Roger Govier "expect_ed" wrote in message ... Yes, that seems to be the problem. Even when I set the format to numeric the cells remain as non-numeric. If I try =value(c10) I get a #Value error. Any suggestions for forcing the cells to numbers??? thanks for your help. ed "Roger Govier" wrote: Yes, I misread your posting. Your dates are in A and the values to be summed are in C. It works absolutely fine for me. There must be something wrong with your data. Perhaps the values in column C are text, not numeric. That would give a result of 0. Try testing columns C with =ISNUMBER(C1) and copying down. -- Regards Roger Govier "expect_ed" wrote in message ... I don't think that is correct. According to Excel the first parameter is the compare range and the 3rd parameter is the cells to be summed. In any case I tried switching it and still get zero. Thanks anyway. ed "Roger Govier" wrote: Hi Ed I think you have the formula the wrong way round. You are saying the "dates" are in C and the values to be summed in A. Try =SUMIF(C10:C100,P1,A10:A100) -- Regards Roger Govier "expect_ed" wrote in message ... I have a column (A) that calculates a date to the nearest 1st of the month based on a date in another column on another sheet (Sheet2!G). The calculation is of the form =VALUE(LEFT(Sheet2!G43,2))+1 & "/1" Column A is formatted as text. Then I have a set of cells (P1 to P8) that calculates the first of the month out 2 to 10 months. That calculation is of the form = (MONTH(TODAY())+#) & "/1" where # varies from 2 to 10. So in column A I get values like 10/1, 12/1, 11/1, etc. and in P I also get similar values. Column P is also formatted as text. In Column C there are work hours associated with the month starts in col A and I want to total the work hours for each month start, so in Column H If have the following calculations: H1: =SUMIF(A10:A100,P1,C10:C100) H2: =SUMIF(A10:A100,P2,C10:C100) etc. So when P2 = 10/1 I would expect H2 to contain the total of any cells in C that have a value of 10/1. Instead I get a zero. I have tried to confirm that the values match with the formula =IF(P2 = A5,1,0) where A5 displays 10/1 and the result is a 1, meaning that the cells match, but I still get only a 0 in H2. Any help appreciated. thanks ed |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif Fails w Date as Text
Hi
Maybe if you change that formula to =IF(Sheet2!M37="",--(Sheet2!L37),--(Sheet2!M37)) Set the format of the receiving ells to hh:mm Set the format of the cell with the Sumif formula to [hh]:mm -- Regards Roger Govier "expect_ed" wrote in message ... Well, I actually simplified that col a bit to shorten the description. The cells in row C actually contain a formula: =IF(Sheet2!M37="",Sheet2!L37,Sheet2!M37) because I am pasting a worksheet into Sheet 2 that will have work hr values either in col L (original) or col M (updated). So when I use the paste special multiply everything changes to #VALUE errors?? The pasted cells in sheet2 come in as General formatted. Thanks again for your help. ed "Roger Govier" wrote: Hi I in a blank cell on your sheet enter 1 Copy that cell Mark the range of your "non numeric data" Paste SpecialMultiply -- Regards Roger Govier "expect_ed" wrote in message ... Yes, that seems to be the problem. Even when I set the format to numeric the cells remain as non-numeric. If I try =value(c10) I get a #Value error. Any suggestions for forcing the cells to numbers??? thanks for your help. ed "Roger Govier" wrote: Yes, I misread your posting. Your dates are in A and the values to be summed are in C. It works absolutely fine for me. There must be something wrong with your data. Perhaps the values in column C are text, not numeric. That would give a result of 0. Try testing columns C with =ISNUMBER(C1) and copying down. -- Regards Roger Govier "expect_ed" wrote in message ... I don't think that is correct. According to Excel the first parameter is the compare range and the 3rd parameter is the cells to be summed. In any case I tried switching it and still get zero. Thanks anyway. ed "Roger Govier" wrote: Hi Ed I think you have the formula the wrong way round. You are saying the "dates" are in C and the values to be summed in A. Try =SUMIF(C10:C100,P1,A10:A100) -- Regards Roger Govier "expect_ed" wrote in message ... I have a column (A) that calculates a date to the nearest 1st of the month based on a date in another column on another sheet (Sheet2!G). The calculation is of the form =VALUE(LEFT(Sheet2!G43,2))+1 & "/1" Column A is formatted as text. Then I have a set of cells (P1 to P8) that calculates the first of the month out 2 to 10 months. That calculation is of the form = (MONTH(TODAY())+#) & "/1" where # varies from 2 to 10. So in column A I get values like 10/1, 12/1, 11/1, etc. and in P I also get similar values. Column P is also formatted as text. In Column C there are work hours associated with the month starts in col A and I want to total the work hours for each month start, so in Column H If have the following calculations: H1: =SUMIF(A10:A100,P1,C10:C100) H2: =SUMIF(A10:A100,P2,C10:C100) etc. So when P2 = 10/1 I would expect H2 to contain the total of any cells in C that have a value of 10/1. Instead I get a zero. I have tried to confirm that the values match with the formula =IF(P2 = A5,1,0) where A5 displays 10/1 and the result is a 1, meaning that the cells match, but I still get only a 0 in H2. Any help appreciated. thanks ed |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif Fails w Date as Text
As soon as I change the formula, I get a #VALUE error.
If it makes any difference, the information I am pasting into Sheet2 is a web page. The numeric values are in what come in as merged cells that are left justified and when checked for format as general. Attempting to convert them using =Value() also results in a #VALUE error. I'm stuck, Thanks for your help. ed "Roger Govier" wrote: Hi Maybe if you change that formula to =IF(Sheet2!M37="",--(Sheet2!L37),--(Sheet2!M37)) Set the format of the receiving ells to hh:mm Set the format of the cell with the Sumif formula to [hh]:mm -- Regards Roger Govier "expect_ed" wrote in message ... Well, I actually simplified that col a bit to shorten the description. The cells in row C actually contain a formula: =IF(Sheet2!M37="",Sheet2!L37,Sheet2!M37) because I am pasting a worksheet into Sheet 2 that will have work hr values either in col L (original) or col M (updated). So when I use the paste special multiply everything changes to #VALUE errors?? The pasted cells in sheet2 come in as General formatted. Thanks again for your help. ed "Roger Govier" wrote: Hi I in a blank cell on your sheet enter 1 Copy that cell Mark the range of your "non numeric data" Paste SpecialMultiply -- Regards Roger Govier "expect_ed" wrote in message ... Yes, that seems to be the problem. Even when I set the format to numeric the cells remain as non-numeric. If I try =value(c10) I get a #Value error. Any suggestions for forcing the cells to numbers??? thanks for your help. ed "Roger Govier" wrote: Yes, I misread your posting. Your dates are in A and the values to be summed are in C. It works absolutely fine for me. There must be something wrong with your data. Perhaps the values in column C are text, not numeric. That would give a result of 0. Try testing columns C with =ISNUMBER(C1) and copying down. -- Regards Roger Govier "expect_ed" wrote in message ... I don't think that is correct. According to Excel the first parameter is the compare range and the 3rd parameter is the cells to be summed. In any case I tried switching it and still get zero. Thanks anyway. ed "Roger Govier" wrote: Hi Ed I think you have the formula the wrong way round. You are saying the "dates" are in C and the values to be summed in A. Try =SUMIF(C10:C100,P1,A10:A100) -- Regards Roger Govier "expect_ed" wrote in message ... I have a column (A) that calculates a date to the nearest 1st of the month based on a date in another column on another sheet (Sheet2!G). The calculation is of the form =VALUE(LEFT(Sheet2!G43,2))+1 & "/1" Column A is formatted as text. Then I have a set of cells (P1 to P8) that calculates the first of the month out 2 to 10 months. That calculation is of the form = (MONTH(TODAY())+#) & "/1" where # varies from 2 to 10. So in column A I get values like 10/1, 12/1, 11/1, etc. and in P I also get similar values. Column P is also formatted as text. In Column C there are work hours associated with the month starts in col A and I want to total the work hours for each month start, so in Column H If have the following calculations: H1: =SUMIF(A10:A100,P1,C10:C100) H2: =SUMIF(A10:A100,P2,C10:C100) etc. So when P2 = 10/1 I would expect H2 to contain the total of any cells in C that have a value of 10/1. Instead I get a zero. I have tried to confirm that the values match with the formula =IF(P2 = A5,1,0) where A5 displays 10/1 and the result is a 1, meaning that the cells match, but I still get only a 0 in H2. Any help appreciated. thanks ed |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif Fails w Date as Text
Hi
there may be non-breaking spaces in the data that is imported. In another column, enter =SUBSTITUTE(SUBSTITUTE(C1," ",""),CHAR(160),"") Try using that as the column of data in your SUMIF() formula -- Regards Roger Govier "expect_ed" wrote in message ... As soon as I change the formula, I get a #VALUE error. If it makes any difference, the information I am pasting into Sheet2 is a web page. The numeric values are in what come in as merged cells that are left justified and when checked for format as general. Attempting to convert them using =Value() also results in a #VALUE error. I'm stuck, Thanks for your help. ed "Roger Govier" wrote: Hi Maybe if you change that formula to =IF(Sheet2!M37="",--(Sheet2!L37),--(Sheet2!M37)) Set the format of the receiving ells to hh:mm Set the format of the cell with the Sumif formula to [hh]:mm -- Regards Roger Govier "expect_ed" wrote in message ... Well, I actually simplified that col a bit to shorten the description. The cells in row C actually contain a formula: =IF(Sheet2!M37="",Sheet2!L37,Sheet2!M37) because I am pasting a worksheet into Sheet 2 that will have work hr values either in col L (original) or col M (updated). So when I use the paste special multiply everything changes to #VALUE errors?? The pasted cells in sheet2 come in as General formatted. Thanks again for your help. ed "Roger Govier" wrote: Hi I in a blank cell on your sheet enter 1 Copy that cell Mark the range of your "non numeric data" Paste SpecialMultiply -- Regards Roger Govier |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif Fails w Date as Text
Roger,
Thanks so much for your persistence. It finally paid off. It must have been the non-breaking spaces. That formula allowed me to get to a working solution. Thanks so much for your help. I'd never heard of non-breaking spaces. Is there a reference anywhere you know of that would provide more info? Thanks again for you help. ed "Roger Govier" wrote: Hi there may be non-breaking spaces in the data that is imported. In another column, enter =SUBSTITUTE(SUBSTITUTE(C1," ",""),CHAR(160),"") Try using that as the column of data in your SUMIF() formula -- Regards Roger Govier |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif Fails w Date as Text
Hi Ed
The Non Breaking Space (nbsp) is encountered a lot when you import data from the Web. Here is a reference that will give you a little more information. http://en.wikipedia.org/wiki/Non-breaking_space -- Regards Roger Govier "expect_ed" wrote in message ... Roger, Thanks so much for your persistence. It finally paid off. It must have been the non-breaking spaces. That formula allowed me to get to a working solution. Thanks so much for your help. I'd never heard of non-breaking spaces. Is there a reference anywhere you know of that would provide more info? Thanks again for you help. ed "Roger Govier" wrote: Hi there may be non-breaking spaces in the data that is imported. In another column, enter =SUBSTITUTE(SUBSTITUTE(C1," ",""),CHAR(160),"") Try using that as the column of data in your SUMIF() formula -- Regards Roger Govier |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula fails if cells text format | Excel Discussion (Misc queries) | |||
| SUMIF within date range as a function of today()'s date | Excel Worksheet Functions | |||
| SUMIF/SUMPRODUCT/IF - 2 criteria - Date and Text | Excel Discussion (Misc queries) | |||
| Formula fails in text formatted cell | Excel Worksheet Functions | |||
| In Excel, why sort function fails when applied to a list of date? | Excel Discussion (Misc queries) | |||
Linear Mode
