| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Another SUMPRODUCT giving the incorrect number
I have another question about a SUMPRODUCT I am using.
I am using Windows XP and Excel 2000. Here is the formula: =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2007,4,1)),--(Data!$A$1:$A$10000<=DATE(2008,3,31)),--(Data!$D$1:$D$10000="Yes"),--(Data!$B$1:$B$10000="Housing Building Services")*SUM(Data!$E$1:$P$10000)) This formula needs to SUM a total of number in cells E1:P10000 where there is a 'Yes' in cells D1:D10000 and 'Housing Building Services' is in cells B1:B10000. At the moment the formula seems to be doing a SUM of all numbers in E1:P10000 and then multiplying that number by 2. The number should be displaying 18 but instead it is displaying 156. Any idea where I am going wrong. Again many thanks for any help with this. -- Richard |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Another SUMPRODUCT giving the incorrect number
Assuming you are summing contents of E$1:$P$10000 where other conditions are
meet: =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2007,4,1)),--(Data!$A$1:$A$10000<=DATE(2008,3,31)),--(Data!$D$1:$D$10000="Yes"),--(Data!$B$1:$B$10000="Housing Building Services")*(Data!$E$1:$P$10000)) "Richard" wrote: I have another question about a SUMPRODUCT I am using. I am using Windows XP and Excel 2000. Here is the formula: =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2007,4,1)),--(Data!$A$1:$A$10000<=DATE(2008,3,31)),--(Data!$D$1:$D$10000="Yes"),--(Data!$B$1:$B$10000="Housing Building Services")*SUM(Data!$E$1:$P$10000)) This formula needs to SUM a total of number in cells E1:P10000 where there is a 'Yes' in cells D1:D10000 and 'Housing Building Services' is in cells B1:B10000. At the moment the formula seems to be doing a SUM of all numbers in E1:P10000 and then multiplying that number by 2. The number should be displaying 18 but instead it is displaying 156. Any idea where I am going wrong. Again many thanks for any help with this. -- Richard |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Another SUMPRODUCT giving the incorrect number
Thank you Toppers.
I have amended the formula. The cell is now displaying #VALUE! I have looked at Help and done a few searches to find out what the problem could be, but can't see what could be wrong. Any ideas. Thanks again. -- Richard "Toppers" wrote: Assuming you are summing contents of E$1:$P$10000 where other conditions are meet: =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2007,4,1)),--(Data!$A$1:$A$10000<=DATE(2008,3,31)),--(Data!$D$1:$D$10000="Yes"),--(Data!$B$1:$B$10000="Housing Building Services")*(Data!$E$1:$P$10000)) "Richard" wrote: I have another question about a SUMPRODUCT I am using. I am using Windows XP and Excel 2000. Here is the formula: =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2007,4,1)),--(Data!$A$1:$A$10000<=DATE(2008,3,31)),--(Data!$D$1:$D$10000="Yes"),--(Data!$B$1:$B$10000="Housing Building Services")*SUM(Data!$E$1:$P$10000)) This formula needs to SUM a total of number in cells E1:P10000 where there is a 'Yes' in cells D1:D10000 and 'Housing Building Services' is in cells B1:B10000. At the moment the formula seems to be doing a SUM of all numbers in E1:P10000 and then multiplying that number by 2. The number should be displaying 18 but instead it is displaying 156. Any idea where I am going wrong. Again many thanks for any help with this. -- Richard |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Another SUMPRODUCT giving the incorrect number
Toppers
I did read on another post that zeros may cause the problem. I do have zeros in the cells E1:E10000 Hope this might help. -- Richard "Toppers" wrote: Assuming you are summing contents of E$1:$P$10000 where other conditions are meet: =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2007,4,1)),--(Data!$A$1:$A$10000<=DATE(2008,3,31)),--(Data!$D$1:$D$10000="Yes"),--(Data!$B$1:$B$10000="Housing Building Services")*(Data!$E$1:$P$10000)) "Richard" wrote: I have another question about a SUMPRODUCT I am using. I am using Windows XP and Excel 2000. Here is the formula: =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2007,4,1)),--(Data!$A$1:$A$10000<=DATE(2008,3,31)),--(Data!$D$1:$D$10000="Yes"),--(Data!$B$1:$B$10000="Housing Building Services")*SUM(Data!$E$1:$P$10000)) This formula needs to SUM a total of number in cells E1:P10000 where there is a 'Yes' in cells D1:D10000 and 'Housing Building Services' is in cells B1:B10000. At the moment the formula seems to be doing a SUM of all numbers in E1:P10000 and then multiplying that number by 2. The number should be displaying 18 but instead it is displaying 156. Any idea where I am going wrong. Again many thanks for any help with this. -- Richard |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Another SUMPRODUCT giving the incorrect number
=SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2007,4,1)),--(Data!$A$1:$A$10000<=DATE(2008,3,31)),--(Data!$D$1:$D$10000="Yes"),--(Data!$B$1:$B$10000="Housing
Building Services"),Data!$E$1:$E$10000+Data!$F$1:$F$10000+D ata!$G$1:$G$10000+Data!$H$1:$H$10000+Data!$I$1:$I$ 10000+Data!$J$1:$J$10000+Data!$K$1:$K$10000+Data!$ L$1:$L$10000+Data!$M$1:$M$10000+Data!$O$1:$O$10000 +Data!$P$1:$P$10000) try the above formula. SUM(Data!$E$1:$P$10000) won't work the way you describe you want it to. It will simply sum all the numbers and add that value each time all the cases are true. I am assuming you want to SUM across for each row, and add that value to the total if all the other cases are true. IF that's the case, then the above formula should work for you. "Richard" wrote: I have another question about a SUMPRODUCT I am using. I am using Windows XP and Excel 2000. Here is the formula: =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2007,4,1)),--(Data!$A$1:$A$10000<=DATE(2008,3,31)),--(Data!$D$1:$D$10000="Yes"),--(Data!$B$1:$B$10000="Housing Building Services")*SUM(Data!$E$1:$P$10000)) This formula needs to SUM a total of number in cells E1:P10000 where there is a 'Yes' in cells D1:D10000 and 'Housing Building Services' is in cells B1:B10000. At the moment the formula seems to be doing a SUM of all numbers in E1:P10000 and then multiplying that number by 2. The number should be displaying 18 but instead it is displaying 156. Any idea where I am going wrong. Again many thanks for any help with this. -- Richard |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Another SUMPRODUCT giving the incorrect number
Thank you Sloth.
You are correct in what I want the formula to do. I used the formula you have kindly done for me, but the cell is still displaying #VALUE! Any ideas? -- Richard "Sloth" wrote: =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2007,4,1)),--(Data!$A$1:$A$10000<=DATE(2008,3,31)),--(Data!$D$1:$D$10000="Yes"),--(Data!$B$1:$B$10000="Housing Building Services"),Data!$E$1:$E$10000+Data!$F$1:$F$10000+D ata!$G$1:$G$10000+Data!$H$1:$H$10000+Data!$I$1:$I$ 10000+Data!$J$1:$J$10000+Data!$K$1:$K$10000+Data!$ L$1:$L$10000+Data!$M$1:$M$10000+Data!$O$1:$O$10000 +Data!$P$1:$P$10000) try the above formula. SUM(Data!$E$1:$P$10000) won't work the way you describe you want it to. It will simply sum all the numbers and add that value each time all the cases are true. I am assuming you want to SUM across for each row, and add that value to the total if all the other cases are true. IF that's the case, then the above formula should work for you. "Richard" wrote: I have another question about a SUMPRODUCT I am using. I am using Windows XP and Excel 2000. Here is the formula: =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2007,4,1)),--(Data!$A$1:$A$10000<=DATE(2008,3,31)),--(Data!$D$1:$D$10000="Yes"),--(Data!$B$1:$B$10000="Housing Building Services")*SUM(Data!$E$1:$P$10000)) This formula needs to SUM a total of number in cells E1:P10000 where there is a 'Yes' in cells D1:D10000 and 'Housing Building Services' is in cells B1:B10000. At the moment the formula seems to be doing a SUM of all numbers in E1:P10000 and then multiplying that number by 2. The number should be displaying 18 but instead it is displaying 156. Any idea where I am going wrong. Again many thanks for any help with this. -- Richard |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Another SUMPRODUCT giving the incorrect number
oRichard,
First aplogies for mis-reading your original formula re the SUM!. Second: column N is missing from the current formula. Third: on a sample of 20 rows I got the correct result: the #VALUE error is likely to be data OR you you have mis-typed a range ... extra or missing zero... as all ranges must be the same size Blank entries or zeros should not cause a problem (I have them in my test data) HTH "Richard" wrote: Thank you Sloth. You are correct in what I want the formula to do. I used the formula you have kindly done for me, but the cell is still displaying #VALUE! Any ideas? -- Richard "Sloth" wrote: =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2007,4,1)),--(Data!$A$1:$A$10000<=DATE(2008,3,31)),--(Data!$D$1:$D$10000="Yes"),--(Data!$B$1:$B$10000="Housing Building Services"),Data!$E$1:$E$10000+Data!$F$1:$F$10000+D ata!$G$1:$G$10000+Data!$H$1:$H$10000+Data!$I$1:$I$ 10000+Data!$J$1:$J$10000+Data!$K$1:$K$10000+Data!$ L$1:$L$10000+Data!$M$1:$M$10000+Data!$O$1:$O$10000 +Data!$P$1:$P$10000) try the above formula. SUM(Data!$E$1:$P$10000) won't work the way you describe you want it to. It will simply sum all the numbers and add that value each time all the cases are true. I am assuming you want to SUM across for each row, and add that value to the total if all the other cases are true. IF that's the case, then the above formula should work for you. "Richard" wrote: I have another question about a SUMPRODUCT I am using. I am using Windows XP and Excel 2000. Here is the formula: =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2007,4,1)),--(Data!$A$1:$A$10000<=DATE(2008,3,31)),--(Data!$D$1:$D$10000="Yes"),--(Data!$B$1:$B$10000="Housing Building Services")*SUM(Data!$E$1:$P$10000)) This formula needs to SUM a total of number in cells E1:P10000 where there is a 'Yes' in cells D1:D10000 and 'Housing Building Services' is in cells B1:B10000. At the moment the formula seems to be doing a SUM of all numbers in E1:P10000 and then multiplying that number by 2. The number should be displaying 18 but instead it is displaying 156. Any idea where I am going wrong. Again many thanks for any help with this. -- Richard |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Another SUMPRODUCT giving the incorrect number
this works .....
=SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2007,4,1))*(Data!$A$1:$A $10000<=DATE(2008,3,31))*(Data!$D$1:$D$10000="Yes" )*(Data!$B$1:$B$10000="Housing Building Services")*Data!$E$1:$P$10000) "Richard" wrote: Thank you Sloth. You are correct in what I want the formula to do. I used the formula you have kindly done for me, but the cell is still displaying #VALUE! Any ideas? -- Richard "Sloth" wrote: =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2007,4,1)),--(Data!$A$1:$A$10000<=DATE(2008,3,31)),--(Data!$D$1:$D$10000="Yes"),--(Data!$B$1:$B$10000="Housing Building Services"),Data!$E$1:$E$10000+Data!$F$1:$F$10000+D ata!$G$1:$G$10000+Data!$H$1:$H$10000+Data!$I$1:$I$ 10000+Data!$J$1:$J$10000+Data!$K$1:$K$10000+Data!$ L$1:$L$10000+Data!$M$1:$M$10000+Data!$O$1:$O$10000 +Data!$P$1:$P$10000) try the above formula. SUM(Data!$E$1:$P$10000) won't work the way you describe you want it to. It will simply sum all the numbers and add that value each time all the cases are true. I am assuming you want to SUM across for each row, and add that value to the total if all the other cases are true. IF that's the case, then the above formula should work for you. "Richard" wrote: I have another question about a SUMPRODUCT I am using. I am using Windows XP and Excel 2000. Here is the formula: =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2007,4,1)),--(Data!$A$1:$A$10000<=DATE(2008,3,31)),--(Data!$D$1:$D$10000="Yes"),--(Data!$B$1:$B$10000="Housing Building Services")*SUM(Data!$E$1:$P$10000)) This formula needs to SUM a total of number in cells E1:P10000 where there is a 'Yes' in cells D1:D10000 and 'Housing Building Services' is in cells B1:B10000. At the moment the formula seems to be doing a SUM of all numbers in E1:P10000 and then multiplying that number by 2. The number should be displaying 18 but instead it is displaying 156. Any idea where I am going wrong. Again many thanks for any help with this. -- Richard |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Another SUMPRODUCT giving the incorrect number
If you have non-numerical data in columns E through P you will have to use
the N() function like this... =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2007,4,1)),--(Data!$A$1:$A$10000<=DATE(2008,3,31)),--(Data!$D$1:$D$10000="Yes"),--(Data!$B$1:$B$10000="Housing Building Services"),N(Data!$E$1:$E$10000)+N(Data!$F$1:$F$10 000)+N(Data!$G$1:$G$10000)+N(Data!$H$1:$H$10000)+N (Data!$I$1:$I$10000)+N(Data!$J$1:$J$10000)+N(Data! $K$1:$K$10000)+N(Data!$L$1:$L$10000)+N(Data!$M$1:$ M$10000)+N(Data!$N$1:$N$10000)+N(Data!$O$1:$O$1000 0)+N(Data!$P$1:$P$10000) It would be easier to use a column to sum E-P across in column Q, and then put Q1:Q10000 in the SUMPRODUCT formula. NOTE: Apparently I never learned my ABC's. I skipped column N in my original formula. :) "Richard" wrote: Thank you Sloth. You are correct in what I want the formula to do. I used the formula you have kindly done for me, but the cell is still displaying #VALUE! Any ideas? -- Richard "Sloth" wrote: =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2007,4,1)),--(Data!$A$1:$A$10000<=DATE(2008,3,31)),--(Data!$D$1:$D$10000="Yes"),--(Data!$B$1:$B$10000="Housing Building Services"),Data!$E$1:$E$10000+Data!$F$1:$F$10000+D ata!$G$1:$G$10000+Data!$H$1:$H$10000+Data!$I$1:$I$ 10000+Data!$J$1:$J$10000+Data!$K$1:$K$10000+Data!$ L$1:$L$10000+Data!$M$1:$M$10000+Data!$O$1:$O$10000 +Data!$P$1:$P$10000) try the above formula. SUM(Data!$E$1:$P$10000) won't work the way you describe you want it to. It will simply sum all the numbers and add that value each time all the cases are true. I am assuming you want to SUM across for each row, and add that value to the total if all the other cases are true. IF that's the case, then the above formula should work for you. "Richard" wrote: I have another question about a SUMPRODUCT I am using. I am using Windows XP and Excel 2000. Here is the formula: =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2007,4,1)),--(Data!$A$1:$A$10000<=DATE(2008,3,31)),--(Data!$D$1:$D$10000="Yes"),--(Data!$B$1:$B$10000="Housing Building Services")*SUM(Data!$E$1:$P$10000)) This formula needs to SUM a total of number in cells E1:P10000 where there is a 'Yes' in cells D1:D10000 and 'Housing Building Services' is in cells B1:B10000. At the moment the formula seems to be doing a SUM of all numbers in E1:P10000 and then multiplying that number by 2. The number should be displaying 18 but instead it is displaying 156. Any idea where I am going wrong. Again many thanks for any help with this. -- Richard |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Another SUMPRODUCT giving the incorrect number
Thank you once again Toppers. The formula works great.
I really appreciate your help. -- Richard "Toppers" wrote: this works ..... =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2007,4,1))*(Data!$A$1:$A $10000<=DATE(2008,3,31))*(Data!$D$1:$D$10000="Yes" )*(Data!$B$1:$B$10000="Housing Building Services")*Data!$E$1:$P$10000) "Richard" wrote: Thank you Sloth. You are correct in what I want the formula to do. I used the formula you have kindly done for me, but the cell is still displaying #VALUE! Any ideas? -- Richard "Sloth" wrote: =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2007,4,1)),--(Data!$A$1:$A$10000<=DATE(2008,3,31)),--(Data!$D$1:$D$10000="Yes"),--(Data!$B$1:$B$10000="Housing Building Services"),Data!$E$1:$E$10000+Data!$F$1:$F$10000+D ata!$G$1:$G$10000+Data!$H$1:$H$10000+Data!$I$1:$I$ 10000+Data!$J$1:$J$10000+Data!$K$1:$K$10000+Data!$ L$1:$L$10000+Data!$M$1:$M$10000+Data!$O$1:$O$10000 +Data!$P$1:$P$10000) try the above formula. SUM(Data!$E$1:$P$10000) won't work the way you describe you want it to. It will simply sum all the numbers and add that value each time all the cases are true. I am assuming you want to SUM across for each row, and add that value to the total if all the other cases are true. IF that's the case, then the above formula should work for you. "Richard" wrote: I have another question about a SUMPRODUCT I am using. I am using Windows XP and Excel 2000. Here is the formula: =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2007,4,1)),--(Data!$A$1:$A$10000<=DATE(2008,3,31)),--(Data!$D$1:$D$10000="Yes"),--(Data!$B$1:$B$10000="Housing Building Services")*SUM(Data!$E$1:$P$10000)) This formula needs to SUM a total of number in cells E1:P10000 where there is a 'Yes' in cells D1:D10000 and 'Housing Building Services' is in cells B1:B10000. At the moment the formula seems to be doing a SUM of all numbers in E1:P10000 and then multiplying that number by 2. The number should be displaying 18 but instead it is displaying 156. Any idea where I am going wrong. Again many thanks for any help with this. -- Richard |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Another SUMPRODUCT giving the incorrect number
Glad it's working and thanks for the feedback.
"Richard" wrote: Thank you once again Toppers. The formula works great. I really appreciate your help. -- Richard "Toppers" wrote: this works ..... =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2007,4,1))*(Data!$A$1:$A $10000<=DATE(2008,3,31))*(Data!$D$1:$D$10000="Yes" )*(Data!$B$1:$B$10000="Housing Building Services")*Data!$E$1:$P$10000) "Richard" wrote: Thank you Sloth. You are correct in what I want the formula to do. I used the formula you have kindly done for me, but the cell is still displaying #VALUE! Any ideas? -- Richard "Sloth" wrote: =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2007,4,1)),--(Data!$A$1:$A$10000<=DATE(2008,3,31)),--(Data!$D$1:$D$10000="Yes"),--(Data!$B$1:$B$10000="Housing Building Services"),Data!$E$1:$E$10000+Data!$F$1:$F$10000+D ata!$G$1:$G$10000+Data!$H$1:$H$10000+Data!$I$1:$I$ 10000+Data!$J$1:$J$10000+Data!$K$1:$K$10000+Data!$ L$1:$L$10000+Data!$M$1:$M$10000+Data!$O$1:$O$10000 +Data!$P$1:$P$10000) try the above formula. SUM(Data!$E$1:$P$10000) won't work the way you describe you want it to. It will simply sum all the numbers and add that value each time all the cases are true. I am assuming you want to SUM across for each row, and add that value to the total if all the other cases are true. IF that's the case, then the above formula should work for you. "Richard" wrote: I have another question about a SUMPRODUCT I am using. I am using Windows XP and Excel 2000. Here is the formula: =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2007,4,1)),--(Data!$A$1:$A$10000<=DATE(2008,3,31)),--(Data!$D$1:$D$10000="Yes"),--(Data!$B$1:$B$10000="Housing Building Services")*SUM(Data!$E$1:$P$10000)) This formula needs to SUM a total of number in cells E1:P10000 where there is a 'Yes' in cells D1:D10000 and 'Housing Building Services' is in cells B1:B10000. At the moment the formula seems to be doing a SUM of all numbers in E1:P10000 and then multiplying that number by 2. The number should be displaying 18 but instead it is displaying 156. Any idea where I am going wrong. Again many thanks for any help with this. -- Richard |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Another SUMPRODUCT giving the incorrect number
Thank you Sloth.
You help is much appreciated. -- Richard "Sloth" wrote: If you have non-numerical data in columns E through P you will have to use the N() function like this... =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2007,4,1)),--(Data!$A$1:$A$10000<=DATE(2008,3,31)),--(Data!$D$1:$D$10000="Yes"),--(Data!$B$1:$B$10000="Housing Building Services"),N(Data!$E$1:$E$10000)+N(Data!$F$1:$F$10 000)+N(Data!$G$1:$G$10000)+N(Data!$H$1:$H$10000)+N (Data!$I$1:$I$10000)+N(Data!$J$1:$J$10000)+N(Data! $K$1:$K$10000)+N(Data!$L$1:$L$10000)+N(Data!$M$1:$ M$10000)+N(Data!$N$1:$N$10000)+N(Data!$O$1:$O$1000 0)+N(Data!$P$1:$P$10000) It would be easier to use a column to sum E-P across in column Q, and then put Q1:Q10000 in the SUMPRODUCT formula. NOTE: Apparently I never learned my ABC's. I skipped column N in my original formula. :) "Richard" wrote: Thank you Sloth. You are correct in what I want the formula to do. I used the formula you have kindly done for me, but the cell is still displaying #VALUE! Any ideas? -- Richard "Sloth" wrote: =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2007,4,1)),--(Data!$A$1:$A$10000<=DATE(2008,3,31)),--(Data!$D$1:$D$10000="Yes"),--(Data!$B$1:$B$10000="Housing Building Services"),Data!$E$1:$E$10000+Data!$F$1:$F$10000+D ata!$G$1:$G$10000+Data!$H$1:$H$10000+Data!$I$1:$I$ 10000+Data!$J$1:$J$10000+Data!$K$1:$K$10000+Data!$ L$1:$L$10000+Data!$M$1:$M$10000+Data!$O$1:$O$10000 +Data!$P$1:$P$10000) try the above formula. SUM(Data!$E$1:$P$10000) won't work the way you describe you want it to. It will simply sum all the numbers and add that value each time all the cases are true. I am assuming you want to SUM across for each row, and add that value to the total if all the other cases are true. IF that's the case, then the above formula should work for you. "Richard" wrote: I have another question about a SUMPRODUCT I am using. I am using Windows XP and Excel 2000. Here is the formula: =SUMPRODUCT(--(Data!$A$1:$A$10000=DATE(2007,4,1)),--(Data!$A$1:$A$10000<=DATE(2008,3,31)),--(Data!$D$1:$D$10000="Yes"),--(Data!$B$1:$B$10000="Housing Building Services")*SUM(Data!$E$1:$P$10000)) This formula needs to SUM a total of number in cells E1:P10000 where there is a 'Yes' in cells D1:D10000 and 'Housing Building Services' is in cells B1:B10000. At the moment the formula seems to be doing a SUM of all numbers in E1:P10000 and then multiplying that number by 2. The number should be displaying 18 but instead it is displaying 156. Any idea where I am going wrong. Again many thanks for any help with this. -- Richard |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMPRODUCT is giving incorrect number | Excel Discussion (Misc queries) | |||
| SUMPRODUCT - Giving me trouble | Excel Worksheet Functions | |||
| Sumproduct giving #NA | Excel Worksheet Functions | |||
| Very large workbook now giving incorrect results :( | Excel Discussion (Misc queries) | |||
| Formula giving incorrect answer... | Excel Discussion (Misc queries) | |||
Linear Mode
