Trouble with SUMPRODUCT

  • Thread starter Thread starter Craig
  • Start date Start date
C

Craig

Hi, I'm using the sumproduct function to extract data from a large data file
that was imported.
Works great, except for when I run the my macro to import the file all the
sumproduct functions display #REF!

Heres the before and after function
=IF('ScanShark Data'!$A$2="","",SUMPRODUCT(--('ScanShark
Data'!$A$2:$A$4000=3),'ScanShark Data'!$F$2:$F$4000,'ScanShark
Data'!$E$2:$E$4000,'ScanShark Data'!$D$2:$D$4000))


=IF('ScanShark Data'!#REF!="","",SUMPRODUCT(--('ScanShark
Data'!#REF!=A5),--('ScanShark Data'!#REF!=B5),'ScanShark
Data'!#REF!,'ScanShark Data'!#REF!,'ScanShark Data'!#REF!))

Is there a way to stop this from happening? As my data will be imported a
few time a day with different data.

Thanks Once Again!
Craig
 
I might add that the data imported will never have the same amount of lines
of Data... could be anywhere from 1000 - 3000.

Craig
 
Just a guess, but it looks like you're deleting the cells (rows or columns).

Maybe you can just clear them instead.

if your code is like:
rows("1:4000").delete
maybe you could use:
rows("1:4000").clear
or
Rows("1:4000").clearcontents

(.clearcontents just clears the values. .clear cleans up format, too.)
 
Craig,

In your import, you're probably using delete unused cells, which is causing
the ref errors. Try right-clicking your data range - Data range properties,
then try clear instead of delete.
 
Thanks guys...
Yes my macro was using the delete cells format, I changed it to
clearcontents... works like a charm.
Thanks Again!
Craig
 
Or you could try using indirect for your range. I just tested this deleting
the rows.
=SUMPRODUCT((INDIRECT("A12:A22")=3)*1)
 
Back
Top