Silly error in sumproduct function in VBA

B

buffalo

Board Regular
Joined
Jun 19, 2003
Messages
183
Somehow this works

Code: 
Sheets("Commissions").Range("A65536").End(xlUp).Offset(0, 1).Value = Application.WorksheetFunction.SumProduct((CommissionRng), (CommissionRng))

But this does not:
Code: 
Sheets("Commissions").Range("A65536").End(xlUp).Offset(0, 1).Value = Application.WorksheetFunction.SumProduct((CommissionRng>50), (CommissionRng))
--> Here i sum only the Commissions > 50...

Anythign wrong in the syntax??

THanks
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Hi,

The SumProduct doesn't support the ">50"

Are you sure it's the Sumproduct you wanna use? or not only the sum.if function?

Assuming commissionrng = A1:A3
Then Sumproduct (A1:A3, A1:A3) = A1*A1 + A2*A2 + A3*A3

If that's what you want, then try this, this should work if you want to put a value in the cell, not a formula :

Code: 
Dim sCom as String
sCom = CommissionRng.Address

With Sheets("Commissions").Range("A65536").End(xlUp).Offset(0, 1)

.FormulaArray = _
        "=SUM(IF(" & sCom & ">50," & sCom & "*" & sCom & ",0))"

.Formula = .Value

End With

But you can let the formula in the cell, so it will autoupdate on any change, to do so, just remove the ".Formula = .Value" part...

Hope this helps,
 
Upvote 0
I don't want to use a formula..its a one-time application only type thing.

Actually I want to do this

Code: 
--------------------------------------------------------------------------------

Sheets("Commissions").Range("A65536").End(xlUp).Offset(0, 1).Value = Application.WorksheetFunction.SumProduct((AccountRng="Manchester"),
(DataRng = 1/3/04), (CommissionRng)) 


--------------------------------------------------------------------------------


Is it not at all possible to do it this way?

THanks


So the sumproduct is not really so cool in the VBA?
 
Upvote 0
Let's say that this is your worksheet:
Book1
ABCD
1Manchester1/3/2004111
2Manchester222
3Manchester333
4Manchester444
5Manchester1/3/2004555
6Manchester666
7Manchester777
8Manchester888
9Manchester1/3/2004999
10Manchester111
11Manchester222
12Manchester1/3/2004333
13Manchester444
14Manchester1/3/2004555
15
162553
Sheet2


You can use this code (the code was written on the sheet module, so there is no reference made to the worksheet in the code):<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> Commission_Total()<SPAN style="color:darkblue">Dim</SPAN> ThisCell<SPAN style="color:darkblue">As</SPAN> Range<SPAN style="color:darkblue">Dim</SPAN> CommTotal<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Long</SPAN>

    <SPAN style="color:darkblue">For</SPAN><SPAN style="color:darkblue">Each</SPAN> ThisCell<SPAN style="color:darkblue">In</SPAN> Range("A1:A" & Range("A65536").End(xlUp).Row)
        <SPAN style="color:darkblue">If</SPAN> ThisCell.Value = "Manchester" And ThisCell.Offset(, 1).Value = #1/3/2004#<SPAN style="color:darkblue">Then</SPAN>
            CommTotal = CommTotal + ThisCell.Offset(, 2).Value
        <SPAN style="color:darkblue">End</SPAN><SPAN style="color:darkblue">If</SPAN>
    <SPAN style="color:darkblue">Next</SPAN> ThisCell
    MsgBox CommTotal<SPAN style="color:darkblue">End</SPAN><SPAN style="color:darkblue">Sub</SPAN></FONT>

Does this help?
 
Upvote 0
phantom1975 said:
...

=SUMPRODUCT((A1:A14="Manchester")*(B1:B14>=1/3/2004),(C1:C14))
Click to expand...

Phantom,

The way you included the date condition will make SumProduct to see it as:

0.000166333998669328

instead of as:

37989

which is a real date.

So, the formula should be:

=SUMPRODUCT((A1:A14="Manchester")*(B1:B14>="1/3/2004"+0),(C1:C14))

or

=SUMPRODUCT(--(A1:A14="Manchester"),--(B1:B14>="1/3/2004"+0),(C1:C14))
 
Upvote 0
Buffalo,
Actually I want to do this:
Sheets("Commissions").Range("A65536").End(xlUp).Offset(0, 1).Value = Application.WorksheetFunction.SumProduct((AccountRng="Manchester"),
(DataRng = 1/3/04), (CommissionRng))

Is it not at all possible to do it this way?
Click to expand...
Both the following work:

Code: 
Sub test1()

Dim x As String
Dim rng As Range
Dim ws As Worksheet

Set ws = Sheets("Commissions")
Set rng = ws.Range("A65536").End(xlUp).Offset(0, 1)

x = Evaluate("=SUMPRODUCT((AccountRng=""Manchester"")*(DataRng = Date(2004,3,1))*(CommissionRng))")

rng = x
End Sub

Code: 
Sub Test2()
Dim rng As Range
Dim ws As Worksheet
Set ws = Sheets("Commissions")
Set rng = ws.Range("A65536").End(xlUp).Offset(0, 1)

With rng
 .FormulaR1C1 = _
    "=SUMPRODUCT((AccountRng=""Manchester"")*(DataRng = ""1/03/04""+0)*(CommissionRng))"

 .Value = .Value
End With
     
End Sub
HTH


Mike
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Macro to Pull Summary from Multiple Files
Replies
2
Views
318
dreid1011
dreid1011
P
  • Solved
Counter in For Each loop
Replies
2
Views
262
Possum17
P
J
  • Question
Copy row from Workbook to another when Checkbox is checked
Replies
0
Views
576
JohnTester
J
I
  • Question
Resize property when transferring data over
2
Replies
17
Views
334
ItalianPlatinum
I
L
  • Solved
VBA Error
2 3
Replies
29
Views
570
Joe4
Joe4

Forum statistics

Threads
1,216,351
Messages
6,130,145
Members
449,560
Latest member
mattstan2012

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top