A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

"#N/A" results from SUMPRODUCT



 
 
Thread Tools Display Modes
  #1  
Old August 30th, 2004, 01:51 PM
Carpie
external usenet poster
 
Posts: n/a
Default "#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  
Old August 30th, 2004, 02:03 PM
Frank Kabel
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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 02:31 AM


All times are GMT +1. The time now is 05:48 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.