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
|
|||
|
|||
"#N/A" results from SUMPRODUCT
I have approximately 10 spreadsheets in which people from multiple locations
enter in savings data each month. There are two columns of particular interest in these spreadsheets. One is YTD Savings which sums the savings of all the month's entries up until the current month. The other column is Year End Forecast which sums all of the months through December. Everything works fine in these spreadsheets. I have an eleventh spreadsheet which pulls data from those 10 spreadsheets for a summary. This eleventh spreadsheet had 10 tabs (one for each spreadsheet) and sums up the totals in the YTD Savings and YE Forecast columns depending on what is in another column (Enabler). To accomplish this, I use the SUMPRODUCT equation. An example of the equations are (removing long file paths): (SUMPRODUCT(--('[LeHavre.xls]LeHavre'!$E$1:$E$20000="Direct Material"),'[LeHavre.xls]LeHavre'!$J$1:$J$20000))/1000000 AND (SUMPRODUCT(--('[LeHavre.xls]LeHavre'!$E$1:$E$20000="Direct Material"),'[LeHavre.xls]LeHavre'!$L$1:$L$20000))/1000000 These sum up the YTD Actual and YE Forecast savings if the Enabler equals "Direct Material". Remember that I use the exact same equation in nine other spreadsheets with nothing different than referencing a different source file. OK, so now the problem. Everything has worked fine with these spreadsheets for the last 5-6 months. Now this month, I get a "#N/A" result in two of my spreadsheets for the YTD Actual summation. The YE Forecast for each of these spreadsheets still works fine! I'm at a complete loss here. At first I thought it might be poor data entry but remember that the YTD Actual and YE Forecast values are not entered data, but equations that sum up entered data. So there is very little chance they have been tweaked. Any thoughts? |
#2
|
|||
|
|||
Hi
see your other post -- Regards Frank Kabel Frankfurt, Germany Carpie wrote: I have approximately 10 spreadsheets in which people from multiple locations enter in savings data each month. There are two columns of particular interest in these spreadsheets. One is YTD Savings which sums the savings of all the month's entries up until the current month. The other column is Year End Forecast which sums all of the months through December. Everything works fine in these spreadsheets. I have an eleventh spreadsheet which pulls data from those 10 spreadsheets for a summary. This eleventh spreadsheet had 10 tabs (one for each spreadsheet) and sums up the totals in the YTD Savings and YE Forecast columns depending on what is in another column (Enabler). To accomplish this, I use the SUMPRODUCT equation. An example of the equations are (removing long file paths): (SUMPRODUCT(--('[LeHavre.xls]LeHavre'!$E$1:$E$20000="Direct Material"),'[LeHavre.xls]LeHavre'!$J$1:$J$20000))/1000000 AND (SUMPRODUCT(--('[LeHavre.xls]LeHavre'!$E$1:$E$20000="Direct Material"),'[LeHavre.xls]LeHavre'!$L$1:$L$20000))/1000000 These sum up the YTD Actual and YE Forecast savings if the Enabler equals "Direct Material". Remember that I use the exact same equation in nine other spreadsheets with nothing different than referencing a different source file. OK, so now the problem. Everything has worked fine with these spreadsheets for the last 5-6 months. Now this month, I get a "#N/A" result in two of my spreadsheets for the YTD Actual summation. The YE Forecast for each of these spreadsheets still works fine! I'm at a complete loss here. At first I thought it might be poor data entry but remember that the YTD Actual and YE Forecast values are not entered data, but equations that sum up entered data. So there is very little chance they have been tweaked. Any thoughts? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Disappearing results | Gilley | Mailmerge | 1 | August 21st, 2004 12:12 PM |
Repost: Calculation problem. Someone help! | Victor | Running & Setting Up Queries | 13 | August 6th, 2004 05:21 PM |
SUMIF vs SUM(IF(..)) vs SUMPRODUCT | Harlan Grove | Worksheet Functions | 7 | July 1st, 2004 10:22 PM |
SUMIF vs SUM(IF(..)) vs SUMPRODUCT | Harlan Grove | General Discussion | 7 | July 1st, 2004 10:22 PM |
SumProduct Counts Blank Results | Joe Gieder | Worksheet Functions | 4 | January 14th, 2004 01:31 AM |