Sumproduct with A1 notation instead of R1C1

[sgltaylor]

Hi,

I would appreciate some help with the following. I am trying to enter
a sumproduct formula in VBA but using A1 notation instead of R1C1.
Could you someone provide me with an example.

Thanks

 

[Per Jessen]

Hi

Look at this:

TargetRange = "A1:A6"
MyCondition = "Per"
Range("C2").Formula = "=sumproduct(--(" & TargetRange & "=""" _
& MyCondition & """),B1:B6)"


Regards,
Per

 

[Bob Phillips]

Activecell.Formula = "=SUMPRODUCT(--(A2:A20="value1"),--(B2:B20=17))

or

Set rng1 = Range("A2:A20")
Set rng2 = Range("B2:B20")

Activecell.Formula = "=SUMPRODUCT(--(" & rng1.Address & "),--(" &
rng2.Address & "))"


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)