# SUMPRODUCT Macro

#### daily106

##### Board Regular
Hello,

I got some help for creating the formular for SUMPRODUCT

http://www.mrexcel.com/board2/viewtopic.php?t=238390&highlight=

Now I am trying to implemet this to the macro. I will have different counts of rows so I named the ranges as follows...

When I run the macro, I get an error for "types mismatch". What am I doing wrong? Thank you very much!!!

Sheets("Code List").Range("B:B").CurrentRegion.Name = "Area"
Sheets("Code List").Range("E:E").CurrentRegion.Name = "Rate"

Set rng = Range("F2")
While rng <> ""
rng.Offset(0, 2).Value = Application.SumProduct(("Area" = rng.Offset(0, -4).Address) * ("Rate" = rng.Offset(0, -4).Address) >= 2) + 0
Set rng = rng.Offset(1, 0)
Wend

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### Norie

##### Well-known Member
You can't use SumProduct in the same way in VBA as you can on a worksheet.

What is it you actually want to do anyway?

Do you just want to put the formula in to the cells, or is it the actual value you want?

#### daily106

##### Board Regular
Thank you Norie!

I've been working on creating a report that calculates...

"what is the lowest rate for each names(LCR)" and "what carriers have the same rate for names(Tied)"

I didnt know I can not use the sumproduct in the VBA just like the formula on the cells. I tried to review some of the works you have posted on the forum and tried to use rng.Offset... but I finally thought the way to solve this is to use sumproduct.

Please let me know if I was clear on this. Thank you!!
LCR MACRO.xls
ABCDEFGH
1ccareacarriernamerateLCRFirstTied
25931GHTcellular0.24000.24001st
35931POPcellular0.25000.2400
45931LOPcellular0.25500.2400
959355GHTCity0.01030.01031st
1059355POPCity0.01060.0103
1159355LOPCity0.01060.0103
1259355BSTCity0.01210.0103
1359381POPMonterrey0.01020.01021st
1459381GHTMonterrey0.01100.0102
1559381LOPMonterrey0.01130.0102
1659381BSTMonterrey0.01300.0102
Code List

#### acw

##### MrExcel MVP
Hi

Enter the formula

=IF(SUMPRODUCT(--(\$D\$2:\$D\$11=D2),--(\$E\$2:\$E\$11=E2))>1,"yes(tied)","")

in H2 and copy down to H12.

Tony

Replies
2
Views
189
Replies
4
Views
263
Replies
1
Views
282
Replies
4
Views
226
Replies
1
Views
598

1,141,934
Messages
5,709,417
Members
421,635
Latest member
mehdi hannechi

### 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.

### Which adblocker are you using?

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