Solved

# Stuck with VBA and SumProduct in Excel

Posted on 2004-04-07

I am lookinfg at implementing obtaining a Sumproduct from read-only Excel sheets. For practice, I used a sheet with 1300 rows and placed the formula

=SUMPRODUCT((RANGE="BOOKS")*(REGION=6101)*(UNITS))

which correctly obtained 19141 (Units sold). I wrote a quick OLEDB Jet4.0 query on the Excel sheet as

SELECT Sum(UNITS) AS n FROM [Data$] WHERE (RANGE="BOOKS" AND REGION=6101);

which also obtained 19141 units. But wishing to stay with Excel I tried VBA

n = application.SUMPRODUCT((RANGE="BOOKS"),(REGION=6101),(UNITS))

If VarType(n) = 10 Then

sProd = "Value Returned Error"

else

sProd = n

end if

which returns error. What am I doing wrong?

stan

P.S. I posted this question earlier, but it doesn't show up, so I am repeating the post. If it duplicates, I apologize.