Using Sumproduct in VBA

I am trying to convert a working Excel Funtion "=SUMPRODUCT((A7:A1000<>"")*(A7:A1000<>0))" to VBA Code as follows:

    Set y = WorksheetFunction.SumProduct(("A7:A1000" <> "") * ("A7:A1000" <> 0))
    Range("A2").Value = y

The problem is I am getting a "Compile Error: Type Mismatch."

I am not familary enough with the SumProduct to understand what is causing the error.  VBA will not allow me to use "On error resume next."

I am attempting to count non-blank cells in range of cells containing cells which contain formulas.

Please advise.

Who is Participating?
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
The SUMPRODUCT function in a worksheet formula is a very powerfull tool for applying array formula conditional logic to problems. From VBA however, the SumProduct function (available via the Application or Application.WorksheetFunctions objects) is not as flexible and complex array constucts cannot be used. The reason is the parameter typing is much more strict from VBA and each parameter passed to SumProduct must be an array - it cannot be a string. While the following example formula:


can be translated into a VBA form:

   MsgBox Application.SumProduct([A1:A10], [B1:B10])

This more complex version:


can not be translated.

To use the more complex forms of SUMPRODUCT syntax, use the Application.Evaluate function:

   MsgBox Application.Evaluate("SUMPRODUCT((A1:A10=1)*B1:B10)")

Tony_RhoadesAuthor Commented:
I understand your explanation of why I cannot use the WorksheetFunction.

If I am following you correctly I should be able to use the following code in VBA:

Range("A2").Value = Application.Evaluate("SumProduct((A7:A1000 <> "") * (A7:A1000 <> 0))")

however it returns #value!

I must be missing something?
Jerry PaladinoConnect With a Mentor Commented:
For the number of non-blank cell you can also try..
Sub CntNonBlanks()
    Set RngCells = Range("A7:A1000")
    For Each rngcell In RngCells
        If rngcell <> "" Then i = i + 1
    Next rngcell
    MsgBox i & " Non-blank Cells in the Range"
End Sub

Open in new window

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

zorvek (Kevin Jones)ConsultantCommented:
The SUMPRODUCT will operate on the active sheet. If the desired target sheet is not active then you need to use external references:

Range("A2").Value = Application.Evaluate("SumProduct((Sheet1!A7:A1000 <> """") * (Sheet1!A7:A1000 <> 0))")

Also, be careful with your double quotes. You have to escape them to use them in a string.

I use the information in the following PAQ: http:/Q_20896095.html    If you read through to the end, several alternative means of using SUMPRODUCT in VBA are shown.

In general:
1) You must use the comma form of SUMPRODUCT rather than multiplying one Boolean expression by another
2) You must coerce the Boolean expression into 1 and 0. SUMPRODUCT won't do it for you.

Tony_RhoadesAuthor Commented:
Thank you both for your help.  Either of these solutions will do the trick
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.