I need help with sumproduct,

[ryan.fitzpatrick3]

Ok I'm pretty frustrated with this. This is my sumproduct formula

My next problem is that when I pull the information it'll use the
sumproduct code which is on sheet1

=SUMPRODUCT(--(1*'Pulled from Access'!$B$7:$B$5420=OJ!$A11),--
(1*'Pulled from Access'!$C$7:$C$5420=OJ!$C11),--(1*'Pulled from
Access'!$I$7:$I$5420=MONTH(DATEVALUE(OJ!E$10&" 1, 2007"))),--('Pulled
from Access'!$J$7:$J$5420=$K$8),--('Pulled from Access'!$H$7:$H
$5420="R"),'Pulled from Access'!$G$7:$G$5420)-SUMPRODUCT(--(1*'Pulled
from Access'!$B$7:$B$5420=OJ!$A11),--(1*'Pulled from Access'!$C$7:$C
$5420=OJ!$C11),--(1*'Pulled from Access'!$I$7:$I
$5420=MONTH(DATEVALUE(OJ!E$10&" 1, 2007"))),--('Pulled from Access'!$J
$7:$J$5420=MID($K$8,3,2)),--('Pulled from Access'!$H$7:$H
$5420="I"),'Pulled from Access'!$G$7:$G$5420)

It works but lately it's not. Basically I have vba to pull access
table information into excel. this works fine. And I have this
sumproduct equation in another sheet referencing the data pulled from
access. It has always worked except for now it's not. It'll show the
values (thus showing that it is working properly) but right before the
calculation ends it turns the the
correct totals into #VALUE. Why does it do this?

 

[Joel]

You need to debug the code using th eformula auditing. Since you have an
error condiution the clipboard appears around the error cell. right click
the clipboard and select Evaluate Formula..


You can also evaluate formula by going to Tools - Formula Auding - Evaluate
formula after click on cell which contains the formula..