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
|
|||
|
|||
Sumproduct issues
Hi folks.
I use sumproduct to sum columns of values after testing against 2 criteria. At first it was working great, and then it stopped working once one cell that I was testing against had a value in it. It's actually become quite finicky. It'd work in some cases, but not in others. My version of sumproduct is: =sumproduct((Col&RowRange = cell a)*(Col&RowRange = cell b)*(Col&RowRangeFinal)) Where Col&RowRange would be a column, and range of rows; cell a, and cell b would be the values that I'm looking for; and the Col&RowRangeFinal would be the column that I'm summing up. I have to ask-- would this be affected by cell formatting? (I've gone so far as to copy and paste cell values from the source regions to the final regions, and it still only works sometimes.) If so, why? It'd be really nice to know, so I can ensure consistency, and continuity. what else would cause this to fail? Oh, and I did try the comma's instead of *'s as shown in the office assistant help file. That killed everything with no sign of errors, or #ref, value, etc....-- i.e., I just got 0.000 for the returned value. Thus far, aside from this issue, it's been a real godsend, so I'd hate to be forced to use something else to solve my reasons for using it to begin with. |
#2
|
|||
|
|||
Sumproduct issues
Hi Steve
Post your actual formula, and a sample of what data appears in the column you are testing against cell a, what data is being tested against cell b and what data exists in Final. -- Regards Roger Govier "SteveDB1" wrote in message ... Hi folks. I use sumproduct to sum columns of values after testing against 2 criteria. At first it was working great, and then it stopped working once one cell that I was testing against had a value in it. It's actually become quite finicky. It'd work in some cases, but not in others. My version of sumproduct is: =sumproduct((Col&RowRange = cell a)*(Col&RowRange = cell b)*(Col&RowRangeFinal)) Where Col&RowRange would be a column, and range of rows; cell a, and cell b would be the values that I'm looking for; and the Col&RowRangeFinal would be the column that I'm summing up. I have to ask-- would this be affected by cell formatting? (I've gone so far as to copy and paste cell values from the source regions to the final regions, and it still only works sometimes.) If so, why? It'd be really nice to know, so I can ensure consistency, and continuity. what else would cause this to fail? Oh, and I did try the comma's instead of *'s as shown in the office assistant help file. That killed everything with no sign of errors, or #ref, value, etc....-- i.e., I just got 0.000 for the returned value. Thus far, aside from this issue, it's been a real godsend, so I'd hate to be forced to use something else to solve my reasons for using it to begin with. |
#3
|
|||
|
|||
Sumproduct issues
Roger,
Here is a sample of actual formula, and data. =sumproduct((Map!$F$4:$F$30=$C3 )*(Map!$E$4:$E$30=$A3)*(Map$B$3:$B$30)) Map! is the worksheet name Column F = John Doe, Sam Jack, etc.... (all names of clients) Column C = same names as Col. F. It however is on another sheet, and is searching data on that sheet Column E = 5 digit numeric values (we classify these as "general" format. These are permit #'s. I say general format because we just want plain numbers with no decimal places.) Column A = same permit #'s as in E. (Same as in C. The data being searched is on another worksheet.) Column B is acreage values. This is a Civil Engineering application, tracking ownership of land, and water rights. As mentioned initially it worked really well in the beginning. After a few weeks it began to become finicky. To give some more info, I've saved the general form of the equation into an *.xlt template in my template directory. I adjust the row range for the specific worksheet length. And finally, I've even deleted columns, cleared formatting, etc... to wipe out any possible corruption that may have defiled the file I work with. I can open a workbook that has never used the formula, and it may, or may not work there. One of the engineers even did a test before he started using it, and tried a variety of values. He was unable to get it to work. I just applied it to my worksheet set, and found it to work immediately. It was only later that it began not working sporadically. Now I'm working on one workbook in which any of the values with a permit number return a 0. I get that what I'm asking is for the routine to search the column F range on the Map worksheet, and compare it to the data in cell C3 on the other worksheet. IF that data exists, it will return a true, or 1. It then looks through all of the data in the Column E range on the Map worksheet, and compares it to the data in cell A3. If the data is there, it returns a true, or 1. If the data is NOT there, it returns a false, or 0. OR, if the data is in one of the cells, and not the other, it will then return false, or 0. For the values that are both true, it will return a true, or 1, and then add the third column set to return a total value. Thus giving me a dataset comparable to true*true*4.5 = 4.5 true*false*2.3 = 0 false*false*2.1 = 0 false*true*1.2 = 0 true*true*6.5 = 6.5 total = 11.00 Let me know if you need more. "Roger Govier" wrote: Hi Steve Post your actual formula, and a sample of what data appears in the column you are testing against cell a, what data is being tested against cell b and what data exists in Final. -- Regards Roger Govier "SteveDB1" wrote in message ... Hi folks. I use sumproduct to sum columns of values after testing against 2 criteria. At first it was working great, and then it stopped working once one cell that I was testing against had a value in it. It's actually become quite finicky. It'd work in some cases, but not in others. My version of sumproduct is: =sumproduct((Col&RowRange = cell a)*(Col&RowRange = cell b)*(Col&RowRangeFinal)) Where Col&RowRange would be a column, and range of rows; cell a, and cell b would be the values that I'm looking for; and the Col&RowRangeFinal would be the column that I'm summing up. I have to ask-- would this be affected by cell formatting? (I've gone so far as to copy and paste cell values from the source regions to the final regions, and it still only works sometimes.) If so, why? It'd be really nice to know, so I can ensure consistency, and continuity. what else would cause this to fail? Oh, and I did try the comma's instead of *'s as shown in the office assistant help file. That killed everything with no sign of errors, or #ref, value, etc....-- i.e., I just got 0.000 for the returned value. Thus far, aside from this issue, it's been a real godsend, so I'd hate to be forced to use something else to solve my reasons for using it to begin with. |
#4
|
|||
|
|||
Sumproduct issues
Hi Steve
I can see nothing that is implicitly wrong with your formula. Your reasoning at the end of your email is absolutely correct and that is how Sumproduct should arrive at your answer. The fault therefore, I believe must lie with the data. Firstly, your Numeric values in column B are they Numeric or could they be text representations of the numbers. Test the values by using a spare column and entering =SUM(B4:B30). Does it give the correct result or 0? If it gives 0, then you could try entering a 1 in another cell, copy it, mark B4:B30 and Paste SpecialMultiply. That should coerce the values from text to numeric. Next test the Permit Numbers. Are they Numbers or Text in MAP!$E and is it a Text or numeric value in A3? Finally, test the names in Map!F, against the value in C3. Try looking at a cell in MAP!F4:F30 that has the same value as you have in C3 and calculate say =LEN(MAP!F4) and =LEN(C3). Are the results the same? If not there may be leading or trailing spaces in either the data or the comparative cell. Apart from the space character Char(32), if the data were copied from another source, it might also contain the Non breaking space character Char(160). In a spare column, enter =SUBSTITUTE(SUBSTITUTE(E4,CHAR(160),"")," ","") and copy down for the length of your data range. Then, copy this new column, and Paste SpecialValues back over the original data in E4:E30. Also, if you are using XL2003, highlight your formula and go to ToolsFormula AuditingEvaluate Formula and step through the formula and you will see in the white pane how Excel interprets each part of the equation in building to an answer. If none of these tests bring success, post back and maybe someone else will have further thoughts. -- Regards Roger Govier "SteveDB1" wrote in message ... Roger, Here is a sample of actual formula, and data. =sumproduct((Map!$F$4:$F$30=$C3 )*(Map!$E$4:$E$30=$A3)*(Map$B$3:$B$30)) Map! is the worksheet name Column F = John Doe, Sam Jack, etc.... (all names of clients) Column C = same names as Col. F. It however is on another sheet, and is searching data on that sheet Column E = 5 digit numeric values (we classify these as "general" format. These are permit #'s. I say general format because we just want plain numbers with no decimal places.) Column A = same permit #'s as in E. (Same as in C. The data being searched is on another worksheet.) Column B is acreage values. This is a Civil Engineering application, tracking ownership of land, and water rights. As mentioned initially it worked really well in the beginning. After a few weeks it began to become finicky. To give some more info, I've saved the general form of the equation into an *.xlt template in my template directory. I adjust the row range for the specific worksheet length. And finally, I've even deleted columns, cleared formatting, etc... to wipe out any possible corruption that may have defiled the file I work with. I can open a workbook that has never used the formula, and it may, or may not work there. One of the engineers even did a test before he started using it, and tried a variety of values. He was unable to get it to work. I just applied it to my worksheet set, and found it to work immediately. It was only later that it began not working sporadically. Now I'm working on one workbook in which any of the values with a permit number return a 0. I get that what I'm asking is for the routine to search the column F range on the Map worksheet, and compare it to the data in cell C3 on the other worksheet. IF that data exists, it will return a true, or 1. It then looks through all of the data in the Column E range on the Map worksheet, and compares it to the data in cell A3. If the data is there, it returns a true, or 1. If the data is NOT there, it returns a false, or 0. OR, if the data is in one of the cells, and not the other, it will then return false, or 0. For the values that are both true, it will return a true, or 1, and then add the third column set to return a total value. Thus giving me a dataset comparable to true*true*4.5 = 4.5 true*false*2.3 = 0 false*false*2.1 = 0 false*true*1.2 = 0 true*true*6.5 = 6.5 total = 11.00 Let me know if you need more. "Roger Govier" wrote: Hi Steve Post your actual formula, and a sample of what data appears in the column you are testing against cell a, what data is being tested against cell b and what data exists in Final. -- Regards Roger Govier "SteveDB1" wrote in message ... Hi folks. I use sumproduct to sum columns of values after testing against 2 criteria. At first it was working great, and then it stopped working once one cell that I was testing against had a value in it. It's actually become quite finicky. It'd work in some cases, but not in others. My version of sumproduct is: =sumproduct((Col&RowRange = cell a)*(Col&RowRange = cell b)*(Col&RowRangeFinal)) Where Col&RowRange would be a column, and range of rows; cell a, and cell b would be the values that I'm looking for; and the Col&RowRangeFinal would be the column that I'm summing up. I have to ask-- would this be affected by cell formatting? (I've gone so far as to copy and paste cell values from the source regions to the final regions, and it still only works sometimes.) If so, why? It'd be really nice to know, so I can ensure consistency, and continuity. what else would cause this to fail? Oh, and I did try the comma's instead of *'s as shown in the office assistant help file. That killed everything with no sign of errors, or #ref, value, etc....-- i.e., I just got 0.000 for the returned value. Thus far, aside from this issue, it's been a real godsend, so I'd hate to be forced to use something else to solve my reasons for using it to begin with. |
#5
|
|||
|
|||
Sumproduct issues
|
#6
|
|||
|
|||
Sumproduct issues
Roger,
Thanks for your reply. Ok.... Map!E column is indeed showing up with a value when I sum the permit values. However, the "other" page permit values add up to zero. I then went into make sure that the formatting was correct. It shows as general. I then checked the cell that I did the sum formula. It was formatted as text. I reset the formatting to number, with zero decimal places. It still remained at zero. I then did as you mentioned, and made each cell an equation. i.e., =1*permit#. My sumproduct cells then changed to the appropriate values. I then removed the equation component, and just left the Permit #'s in each cell, and the values over in the sumproduct column remained. It apepars to have "forced" the solution, and then just left it alone once it was changed to only the permit numbers by removing the =1* portion. At this point I spoke with the other engineer I made mention of, and he said that this was odd, and that the sumproduct should be able to just read the values in the cells being tested, and not look at formatting, or anything else. And quite frankly, I agree with him. What does it take to make sufficient modifications to the general form of the equation within the background of excel to have it look only at cell contents, and not any other aspect or property of that cell? This is an excellent routine, but its ruined by its looking at all the properties of the cell, instead of just the contents. Is there something that can be done on MS' side to modify this, or ....? Again, thank you. and you too Don. "Roger Govier" wrote: Hi Steve I can see nothing that is implicitly wrong with your formula. Your reasoning at the end of your email is absolutely correct and that is how Sumproduct should arrive at your answer. The fault therefore, I believe must lie with the data. Firstly, your Numeric values in column B are they Numeric or could they be text representations of the numbers. Test the values by using a spare column and entering =SUM(B4:B30). Does it give the correct result or 0? If it gives 0, then you could try entering a 1 in another cell, copy it, mark B4:B30 and Paste SpecialMultiply. That should coerce the values from text to numeric. Next test the Permit Numbers. Are they Numbers or Text in MAP!$E and is it a Text or numeric value in A3? Finally, test the names in Map!F, against the value in C3. Try looking at a cell in MAP!F4:F30 that has the same value as you have in C3 and calculate say =LEN(MAP!F4) and =LEN(C3). Are the results the same? If not there may be leading or trailing spaces in either the data or the comparative cell. Apart from the space character Char(32), if the data were copied from another source, it might also contain the Non breaking space character Char(160). In a spare column, enter =SUBSTITUTE(SUBSTITUTE(E4,CHAR(160),"")," ","") and copy down for the length of your data range. Then, copy this new column, and Paste SpecialValues back over the original data in E4:E30. Also, if you are using XL2003, highlight your formula and go to ToolsFormula AuditingEvaluate Formula and step through the formula and you will see in the white pane how Excel interprets each part of the equation in building to an answer. If none of these tests bring success, post back and maybe someone else will have further thoughts. -- Regards Roger Govier "SteveDB1" wrote in message ... Roger, Here is a sample of actual formula, and data. =sumproduct((Map!$F$4:$F$30=$C3 )*(Map!$E$4:$E$30=$A3)*(Map$B$3:$B$30)) Map! is the worksheet name Column F = John Doe, Sam Jack, etc.... (all names of clients) Column C = same names as Col. F. It however is on another sheet, and is searching data on that sheet Column E = 5 digit numeric values (we classify these as "general" format. These are permit #'s. I say general format because we just want plain numbers with no decimal places.) Column A = same permit #'s as in E. (Same as in C. The data being searched is on another worksheet.) Column B is acreage values. This is a Civil Engineering application, tracking ownership of land, and water rights. As mentioned initially it worked really well in the beginning. After a few weeks it began to become finicky. To give some more info, I've saved the general form of the equation into an *.xlt template in my template directory. I adjust the row range for the specific worksheet length. And finally, I've even deleted columns, cleared formatting, etc... to wipe out any possible corruption that may have defiled the file I work with. I can open a workbook that has never used the formula, and it may, or may not work there. One of the engineers even did a test before he started using it, and tried a variety of values. He was unable to get it to work. I just applied it to my worksheet set, and found it to work immediately. It was only later that it began not working sporadically. Now I'm working on one workbook in which any of the values with a permit number return a 0. I get that what I'm asking is for the routine to search the column F range on the Map worksheet, and compare it to the data in cell C3 on the other worksheet. IF that data exists, it will return a true, or 1. It then looks through all of the data in the Column E range on the Map worksheet, and compares it to the data in cell A3. If the data is there, it returns a true, or 1. If the data is NOT there, it returns a false, or 0. OR, if the data is in one of the cells, and not the other, it will then return false, or 0. For the values that are both true, it will return a true, or 1, and then add the third column set to return a total value. Thus giving me a dataset comparable to true*true*4.5 = 4.5 true*false*2.3 = 0 false*false*2.1 = 0 false*true*1.2 = 0 true*true*6.5 = 6.5 total = 11.00 Let me know if you need more. "Roger Govier" wrote: Hi Steve Post your actual formula, and a sample of what data appears in the column you are testing against cell a, what data is being tested against cell b and what data exists in Final. -- Regards Roger Govier "SteveDB1" wrote in message ... Hi folks. I use sumproduct to sum columns of values after testing against 2 criteria. At first it was working great, and then it stopped working once one cell that I was testing against had a value in it. It's actually become quite finicky. It'd work in some cases, but not in others. My version of sumproduct is: =sumproduct((Col&RowRange = cell a)*(Col&RowRange = cell b)*(Col&RowRangeFinal)) Where Col&RowRange would be a column, and range of rows; cell a, and cell b would be the values that I'm looking for; and the Col&RowRangeFinal would be the column that I'm summing up. I have to ask-- would this be affected by cell formatting? (I've gone so far as to copy and paste cell values from the source regions to the final regions, and it still only works sometimes.) If so, why? It'd be really nice to know, so I can ensure consistency, and continuity. what else would cause this to fail? Oh, and I did try the comma's instead of *'s as shown in the office assistant help file. That killed everything with no sign of errors, or #ref, value, etc....-- i.e., I just got 0.000 for the returned value. Thus far, aside from this issue, it's been a real godsend, so I'd hate to be forced to use something else to solve my reasons for using it to begin with. |
#7
|
|||
|
|||
Sumproduct issues
Hi Steve
Glad you got it working. No, there is nothing wrong with the behaviour of Excel or Sumproduct. How values are stored internally by Excel is different to how they are displayed or portrayed to the user through formatting. For example, if you enter 17/11/2006 in a cell (UK date format) it will be stored by Excel as the number of days since 31 12 1899 which is 39038 a numeric value. It will display as 17/11/2006 which looks like text, or through formatting, you can change the appearance to 17 Nov 2006 or 17 November 2006 or November or a variety of different things. None of these "cosmetic" changes will affect how the value is stored 39038. Now if you enter '17 Nov 2006 it will display as 17 Nov 2006 and will be stored as a string "17 Nov 2006". Excel will allow you to enter ' 31 Nov 2006 as it is just a string or '17 Roger's Month 2006 but trying to enter these latter two as dates would be rejected as they are invalid. If Excel did a comparison between a cell holding 17 Nov 2006 (true Excel date) and '17 Nov 2006 it would quite rightly say that the match was False, as it is comparing a numeric value 39038 with a string "17 Nov 2006" Changing the format of a cell, does not necessarily alter any contents already entered, but will treat any entries made after formatting in the manner required. If in a cell formatted General (the default setting), you enter a 1, it will be treated as numeric and you will see the number in the cell right justified. If you prefix the entry with a single quote '1 the quote will not show in the cell, and the number will be a text 1 and will be left justified and will be stored as the string "1". If you format the cell as Text, then enter a 1 without any preceding quote, then it will be treated as Text and stored as the string "1" If your reference numbers were greater than 15 digits in length, then they would have to entered as Text, as Excel only supports 15 digits for numerical entry. Now, in Sumproduct, you were asking it to do comparisons of each cell in the range E4:E30 with the value in cell A3. If A3 had 123456 as a number and E4 had "123456" as text, then they are not the same and a False is returned If E4:E30 were text values, then provided your entry in A3 was a text value, all would have worked. I didn't actually suggest that you converted those values to numeric, it was the acreages in column B that "had" to be numeric, otherwise they could not be summed. I just suggested that you checked that both the values were text or numeric although I perhaps had not made this clear enough. Provided they are both of the same format, it does not matter what the format is, then the comparison can be made as to whether they are equal to each other or not. The original entries may have been copied and pasted from another source, which was text and that is why they will have been text. Or, there may be some values which are greater than 15 digits, which would therefore have required them all to be entered as Text. If any were greater than 16 digits, they will now be incorrect. 1234567890123456789 would be changed to 1234567890123450000 when you multiplied by 1. Make sure you check this out. If they are greater than 15, you will need to correct the column back to text and type the final digits again, and change cell A3 to text, so when you enter the value you wish to be compared it is a text format. I hope this clarifies things. -- Regards Roger Govier "SteveDB1" wrote in message ... Roger, Thanks for your reply. Ok.... Map!E column is indeed showing up with a value when I sum the permit values. However, the "other" page permit values add up to zero. I then went into make sure that the formatting was correct. It shows as general. I then checked the cell that I did the sum formula. It was formatted as text. I reset the formatting to number, with zero decimal places. It still remained at zero. I then did as you mentioned, and made each cell an equation. i.e., =1*permit#. My sumproduct cells then changed to the appropriate values. I then removed the equation component, and just left the Permit #'s in each cell, and the values over in the sumproduct column remained. It apepars to have "forced" the solution, and then just left it alone once it was changed to only the permit numbers by removing the =1* portion. At this point I spoke with the other engineer I made mention of, and he said that this was odd, and that the sumproduct should be able to just read the values in the cells being tested, and not look at formatting, or anything else. And quite frankly, I agree with him. What does it take to make sufficient modifications to the general form of the equation within the background of excel to have it look only at cell contents, and not any other aspect or property of that cell? This is an excellent routine, but its ruined by its looking at all the properties of the cell, instead of just the contents. Is there something that can be done on MS' side to modify this, or ....? Again, thank you. and you too Don. "Roger Govier" wrote: Hi Steve I can see nothing that is implicitly wrong with your formula. Your reasoning at the end of your email is absolutely correct and that is how Sumproduct should arrive at your answer. The fault therefore, I believe must lie with the data. Firstly, your Numeric values in column B are they Numeric or could they be text representations of the numbers. Test the values by using a spare column and entering =SUM(B4:B30). Does it give the correct result or 0? If it gives 0, then you could try entering a 1 in another cell, copy it, mark B4:B30 and Paste SpecialMultiply. That should coerce the values from text to numeric. Next test the Permit Numbers. Are they Numbers or Text in MAP!$E and is it a Text or numeric value in A3? Finally, test the names in Map!F, against the value in C3. Try looking at a cell in MAP!F4:F30 that has the same value as you have in C3 and calculate say =LEN(MAP!F4) and =LEN(C3). Are the results the same? If not there may be leading or trailing spaces in either the data or the comparative cell. Apart from the space character Char(32), if the data were copied from another source, it might also contain the Non breaking space character Char(160). In a spare column, enter =SUBSTITUTE(SUBSTITUTE(E4,CHAR(160),"")," ","") and copy down for the length of your data range. Then, copy this new column, and Paste SpecialValues back over the original data in E4:E30. Also, if you are using XL2003, highlight your formula and go to ToolsFormula AuditingEvaluate Formula and step through the formula and you will see in the white pane how Excel interprets each part of the equation in building to an answer. If none of these tests bring success, post back and maybe someone else will have further thoughts. -- Regards Roger Govier "SteveDB1" wrote in message ... Roger, Here is a sample of actual formula, and data. =sumproduct((Map!$F$4:$F$30=$C3 )*(Map!$E$4:$E$30=$A3)*(Map$B$3:$B$30)) Map! is the worksheet name Column F = John Doe, Sam Jack, etc.... (all names of clients) Column C = same names as Col. F. It however is on another sheet, and is searching data on that sheet Column E = 5 digit numeric values (we classify these as "general" format. These are permit #'s. I say general format because we just want plain numbers with no decimal places.) Column A = same permit #'s as in E. (Same as in C. The data being searched is on another worksheet.) Column B is acreage values. This is a Civil Engineering application, tracking ownership of land, and water rights. As mentioned initially it worked really well in the beginning. After a few weeks it began to become finicky. To give some more info, I've saved the general form of the equation into an *.xlt template in my template directory. I adjust the row range for the specific worksheet length. And finally, I've even deleted columns, cleared formatting, etc... to wipe out any possible corruption that may have defiled the file I work with. I can open a workbook that has never used the formula, and it may, or may not work there. One of the engineers even did a test before he started using it, and tried a variety of values. He was unable to get it to work. I just applied it to my worksheet set, and found it to work immediately. It was only later that it began not working sporadically. Now I'm working on one workbook in which any of the values with a permit number return a 0. I get that what I'm asking is for the routine to search the column F range on the Map worksheet, and compare it to the data in cell C3 on the other worksheet. IF that data exists, it will return a true, or 1. It then looks through all of the data in the Column E range on the Map worksheet, and compares it to the data in cell A3. If the data is there, it returns a true, or 1. If the data is NOT there, it returns a false, or 0. OR, if the data is in one of the cells, and not the other, it will then return false, or 0. For the values that are both true, it will return a true, or 1, and then add the third column set to return a total value. Thus giving me a dataset comparable to true*true*4.5 = 4.5 true*false*2.3 = 0 false*false*2.1 = 0 false*true*1.2 = 0 true*true*6.5 = 6.5 total = 11.00 Let me know if you need more. "Roger Govier" wrote: Hi Steve Post your actual formula, and a sample of what data appears in the column you are testing against cell a, what data is being tested against cell b and what data exists in Final. -- Regards Roger Govier "SteveDB1" wrote in message ... Hi folks. I use sumproduct to sum columns of values after testing against 2 criteria. At first it was working great, and then it stopped working once one cell that I was testing against had a value in it. It's actually become quite finicky. It'd work in some cases, but not in others. My version of sumproduct is: =sumproduct((Col&RowRange = cell a)*(Col&RowRange = cell b)*(Col&RowRangeFinal)) Where Col&RowRange would be a column, and range of rows; cell a, and cell b would be the values that I'm looking for; and the Col&RowRangeFinal would be the column that I'm summing up. I have to ask-- would this be affected by cell formatting? (I've gone so far as to copy and paste cell values from the source regions to the final regions, and it still only works sometimes.) If so, why? It'd be really nice to know, so I can ensure consistency, and continuity. what else would cause this to fail? Oh, and I did try the comma's instead of *'s as shown in the office assistant help file. That killed everything with no sign of errors, or #ref, value, etc....-- i.e., I just got 0.000 for the returned value. Thus far, aside from this issue, it's been a real godsend, so I'd hate to be forced to use something else to solve my reasons for using it to begin with. |
#8
|
|||
|
|||
Sumproduct issues
Ok. upon further investigation, and various "trials" I've found that by
"forcing" a solution, by means of inputting a value, such as =0, or =1*cell_contents I can obtain the correct values. To me, and others I've mentioned this to, it makes no sense. There are times that I can have identical cell contents for different cells, and it will not recognize them. As such it will give me "individual" totals, instead of summing the contents as is sought. If I take one of the cell's values, and "coerce it" the different values will all be recognised under each of the cells that the sumproduct formula is in, and I then must remove the duplicates to get my total. All of this brings me back to the issue of why doesn't sumproduct just look at the cell contents, instead of the formatting, or other properties of that cell? What does it take to get it to always look at JUST THE CELL CONTENTS, and none of the other properties-- unless I specify those specific properties? This formula will work on the source page, but when I go to move it to a secondary, or tertiary page it will not recognize the cells being tested against due to these issues. grrrrr! Somewhere in the background I can hear Elmer Fudd saying, "scwewy wabbit!!!" "SteveDB1" wrote: Hi folks. I use sumproduct to sum columns of values after testing against 2 criteria. At first it was working great, and then it stopped working once one cell that I was testing against had a value in it. It's actually become quite finicky. It'd work in some cases, but not in others. My version of sumproduct is: =sumproduct((Col&RowRange = cell a)*(Col&RowRange = cell b)*(Col&RowRangeFinal)) Where Col&RowRange would be a column, and range of rows; cell a, and cell b would be the values that I'm looking for; and the Col&RowRangeFinal would be the column that I'm summing up. I have to ask-- would this be affected by cell formatting? (I've gone so far as to copy and paste cell values from the source regions to the final regions, and it still only works sometimes.) If so, why? It'd be really nice to know, so I can ensure consistency, and continuity. what else would cause this to fail? Oh, and I did try the comma's instead of *'s as shown in the office assistant help file. That killed everything with no sign of errors, or #ref, value, etc....-- i.e., I just got 0.000 for the returned value. Thus far, aside from this issue, it's been a real godsend, so I'd hate to be forced to use something else to solve my reasons for using it to begin with. |
#9
|
|||
|
|||
Sumproduct issues
SteveDB1 wrote...
.... Here is a sample of actual formula, and data. =sumproduct((Map!$F$4:$F$30=$C3 )*(Map!$E$4:$E$30=$A3)*(Map$B$3:$B$30)) .... 1st and 2nd ranges run from row 4 to row 30, but 3rd range (note the typo: missing ! between Map and $B) runs from row 3 to row 30. The product of the terms inside SUMPRODUCT will generate an #N/A as the last item in the result array. The missing ! will prevent Excel from even allowing you to enter it (so you didn't paste in your actual formula, did you?), but that's presumably not the problem you're having. Once you correct that typo, the mismatched rows would cause SUMPRODUCT to return #N/A rather than 0. |
#10
|
|||
|
|||
Sumproduct issues
SteveDB1 wrote...
Ok. upon further investigation, and various "trials" I've found that by "forcing" a solution, by means of inputting a value, such as =0, or =1*cell_contents I can obtain the correct values. This means the cells' original contents evaluated as text rather than numbers. That so, changing number format would have no effect whatsoever on the values in those cells. Also, as others have pointed out, Excel will treat numbers and text that might look the same as different values, e.g., =(12345="12345") will return FALSE. To me, and others I've mentioned this to, it makes no sense. Because you're failing to understand that in Excel text values never equal numeric values even if they appear identical. The data type difference matters, appearance doesn't. There are times that I can have identical cell contents for different cells, .... They only APPEAR identical, but they're not. If you believe you're only working with numbers, then force all values to numbers. =SUMPRODUCT((-range1=-cellX)*(-range2=-cellY)*range3) values, and "coerce it" the different values will all be recognised under each of the cells that the sumproduct formula is in, and I then must remove the duplicates to get my total. No, just coerce them within the SUMPRODUCT call, as in the formula above. All of this brings me back to the issue of why doesn't sumproduct just look at the cell contents, instead of the formatting, or other properties of that cell? .... It does. YOU just have to understand that Excel provides no visible cues to distinguish cells containing text from cells containing numbers. You may have cells that APPEAR to contain numbers, but they COULD contain text. Since there are times when it could be useful for cells to contain text composed of nothing but decimal numerals, Excel's ability to distinguish between the number 12345 and the text string 12345 is NOT a flaw. It's just a latent opportunity for you to learn something more about Excel. IOW, Excel *IS* just using the cell contents, but Excel makes a distinction between different data types that merely appear to have the same value when displayed as text. What does it take to get it to always look at JUST THE CELL CONTENTS, and none of the other properties-- unless I specify those specific properties? .... Again, see the formula above. It's *YOUR* permit numbers that aren't numeric. If you want them to be, then it's *YOUR* responsibility to make them so. Alternatively, compare them to text values. So either (--Map!$E$4:$E$30=$A3) or (Map!$E$4:$E$30=$A3&"") |
Thread Tools | |
Display Modes | |
|
|