Type Mismatch Using SumProduct

the

New Member
Joined
Jan 24, 2012
Messages
12
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!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,710
Office Version
2007
Platform
Windows
try

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

the

New Member
Joined
Jan 24, 2012
Messages
12
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:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,710
Office Version
2007
Platform
Windows
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


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:38.02px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">-4</td><td > </td><td style="text-align:right; ">1</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">-5</td><td > </td><td style="text-align:right; ">2</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">3</td><td > </td><td style="text-align:right; ">3</td><td style="text-align:right; ">9</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">3</td><td > </td><td style="text-align:right; ">4</td><td style="text-align:right; ">12</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">6</td><td > </td><td style="text-align:right; ">5</td><td style="text-align:right; ">30</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">-7</td><td > </td><td style="text-align:right; ">6</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">6</td><td > </td><td style="text-align:right; ">7</td><td style="text-align:right; ">42</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">4</td><td > </td><td style="text-align:right; ">8</td><td style="text-align:right; ">32</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">3</td><td > </td><td style="text-align:right; ">9</td><td style="text-align:right; ">27</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">0</td><td > </td><td style="text-align:right; ">10</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td > </td><td > </td><td style="text-align:right; ">152</td><td style="text-align:right; ">152</td><td style="text-align:right; ">152</td><td style="text-align:right; ">152</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D3</td><td >=A3*C3</td></tr><tr><td >D4</td><td >=A4*C4</td></tr><tr><td >D5</td><td >=A5*C5</td></tr><tr><td >D7</td><td >=A7*C7</td></tr><tr><td >D8</td><td >=A8*C8</td></tr><tr><td >D9</td><td >=A9*C9</td></tr><tr><td >D11</td><td >=SUM(D1:D10)</td></tr><tr><td >E11</td><td >=SUMPRODUCT(--(rng_1>0),rng_1,rng_2)</td></tr><tr><td >F11</td><td >{=SUM(IF(rng_1>0,rng_1*rng_2))}</td></tr><tr><td >G11</td><td >=SUMPRODUCT((rng_1>0)*(rng_1*rng_2))</td></tr></table></td></tr></table>



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
 
  • Like
Reactions: the

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,710
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,085,844
Messages
5,386,303
Members
401,992
Latest member
CleverHopper

Some videos you may like

This Week's Hot Topics

Top