| Home |
| Search |
| Today's Posts |
|
#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 |
| 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) | |||
Threaded Mode
