Results 1 to 6 of 6

Thread: Type Mismatch Using SumProduct

  1. #1
    New Member
    Join Date
    Jan 2012
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Type Mismatch Using SumProduct

    Hello,


    Let's say rng1 is some range like A1:A10, and rng2 is some range like B1:B10.


    I am trying to do the Application.WorksheetFunction.Sumproduct and it is giving me a type mismatch. I have:


    Application.WorksheetFunction.SumProduct(--(rng1 > 0), rng1, rng2)


    I only want to sumproduct positive numbers in rng1. This works in the regular spreadsheet but it's not working in VBA. Seems like the --(rng1 >0) is messing it up but not sure why...


    Thanks!

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,207
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Type Mismatch Using SumProduct

    try

    Code:
    Sub test3()
      Dim res As Variant
      res = [SUM(if(rng_1>0,rng_1*rng_2))]
    End Sub
    Regards Dante Amor

  3. #3
    New Member
    Join Date
    Jan 2012
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Type Mismatch Using SumProduct

    I still am getting the same error. I have in one worksheet:

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Set rng2 = Target
    Call sub1
    
    
    End Sub
    I have a general module:

    Code:
    Option Explicit
    
    Public rng2 As Range, rng1 As Range
    
    
    Sub sub1()
    
    
    Set rng1= rng2 .Offset(0, -2)
    
    
    Dim test As Variant
    test = [SUM(if(rng1> 0, rng1* rng2 ))]
    
    
    
    
    End Sub
    Last edited by the; Aug 30th, 2019 at 09:20 AM.

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,207
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Type Mismatch Using SumProduct

    That is not what I had in mind.
    What I assumed is the following. (Just for you to test my code), do the following:
    - Select cells A1:A10, in the Name Box type rng_1
    - Select C1:C10, in the Name Box type rng_2.
    Example


     ABCDEFG
    1-4 1    
    2-5 2    
    33 39   
    43 412   
    56 530   
    6-7 6    
    76 742   
    84 832   
    93 927   
    100 10    
    11   152152152152

    CellFormula
    D3=A3*C3
    D4=A4*C4
    D5=A5*C5
    D7=A7*C7
    D8=A8*C8
    D9=A9*C9
    D11=SUM(D1:D10)
    E11=SUMPRODUCT(--(rng_1>0),rng_1,rng_2)
    F11{=SUM(IF(rng_1>0,rng_1*rng_2))}
    G11=SUMPRODUCT((rng_1>0)*(rng_1*rng_2))




    Formulas from D3 to D9 multiply the numbers greater than 0. And in cell D11 we have the sum.
    The formulas G11 and E11 are the same.
    Formula F11 is array formula. (Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself)

    -----------------
    Now, If you run this code the result is 152

    Code:
    Sub test3()
      Dim res As Variant
      res = [SUM(if(rng_1>0,rng_1*rng_2))]
    End Sub
    ------------------

    And now, For your code to work, it must be like this:

    Code:
    Option Explicit
    Public rng2 As Range, rng1 As Range
    
    
    Sub sub1()
      Set rng1 = rng2.Offset(0, -2)
      Dim test As Variant
      test = Evaluate("=SUM(if(" & rng1.Address & "> 0," & rng1.Address & "*" & rng2.Address & "))")
    End Sub
    Select cells C1 to C10 and the result will be 152
    Regards Dante Amor

  5. #5
    New Member
    Join Date
    Jan 2012
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Type Mismatch Using SumProduct

    Wow this is amazing, and yes it works. Thanks so much!!!

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,207
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Type Mismatch Using SumProduct

    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •