Sum Product Type mismatch error

Billy C

Board Regular
Can anyone tell me why I am getting a Type mismatch error please

Sub SumProduct()
Dim WF As WorksheetFunction
Set WF = Application.WorksheetFunction
Dim Product As Range, Region As Range, Rep As Range, Cust As Range, Sales As Range, COGS As Range
Set Product = Range("B2:B106") 'contains text
Set Region = Range("C2:C106") 'contains text
Set Rep = Range("D2:D106") 'contains text
Set Cust = Range("E2:E106") 'contains text
Set Sales = Range("G2:G106") 'contains values
Set COGS = Range("H2:H106") 'contains values

ActiveCell.Formula = WF.SumProduct((Region = East) * Sales)

End Sub

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
That line isn't really putting a formula in the cell, it's trying to use VBA's version of sumproduct to put it's result in the cell.
The VBA version of Sumproduct can't do the criteria type of evaluations like we use it for in formulas.

Try

or

Sorry to be a nuisance, your code worked when I was doing a sumif from the same workbook but when I tried to update your code to account for between two workbooks I came unstuck
DataFile is the workbook containing the Range and Sum Range, MnRegNo is within the main workbook which holds the Criteria

Can you help?

ActiveCell.Formula = "=SUMIF(" & [DataFile] & ShName & DtaRegNo.Address & "," & MnRegNo.Address(0, 0) & "," & [DataFile] & ShName & SrVals.Address & ")"

Replies
3
Views
243
Replies
30
Views
1K
Replies
6
Views
713
Replies
7
Views
294
Replies
3
Views
429

1,196,385
Messages
6,014,980
Members
441,860
Latest member
Store154

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.

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

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