# Thread: Sumproduct Function in VBA

1. Registered User
Join Date
Oct 2013
Posts
165

## Unanswered: Sumproduct Function in VBA

Dear Seniors,

I had created a Array sum product formula with multiple conditions and it works fine. Now I would like to get help from this forum to create the same formula in Excel VBA. Presently I am using named ranges in my formula. But I would like to use the formula by selecting the dynamic ranges.

Here is the formula I am using to calculate my result based on the Date in C1

{=SUMPRODUCT(IF(((CPYFORIFD>0)*(CPYFORIFD<=C\$1)),1 ,IF(((CPYFORIFA>0)*(CPYFORIFA<=C\$1)),0.8,IF(((CPYF ORIFR>0)*(CPYFORIFR<=C\$1)),0.6,0)))*(CPYWF))}

where us CPYFORIFD, CPYFORIFA, CPYFORIFR are Named ranges which contains Dates & CPYWF is a Number.

2. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805
Hi

If I interpret you formula correctly I don't think you need SumProduct, it could just be nested IFs ie

=IF(((CPYFORIFD>0)*(CPYFORIFD<=C\$1)),1,IF(((CPYFOR IFA>0)*(CPYFORIFA<=C\$1)),0.8,IF(((CPYFORIFR>0)*(CP YFORIFR<=C\$1)),0.6,0)))*(CPYWF)

If this is so, then VBA could look something like this??

Code:
```Function VBA_Function() As Single
Select Case True
Case Is = Range("CPYFORIFD") > 0 And Range("CPYFORIFD") <= Range("C1")
VBA_Function = 1 * Range("CPYWF")
Case Is = Range("CPYFORIFA") > 0 And Range("CPYFORIFA") <= Range("C1")
VBA_Function = 0.8 * Range("CPYWF")
Case Is = Range("CPYFORIFR") > 0 And Range("CPYFORIFR") <= Range("C1")
VBA_Function = 0.6 * Range("CPYWF")
Case Else
VBA_Function = 0
End Select
End Function```
HTH

MTB

3. Registered User
Join Date
Oct 2013
Posts
165
Hi Mike,

Also I want the name range should be dynamic one. (i.e.) I want to use this formula in different worksheets. I want the range should be selected whenever I am executing this formula.

In mean time I will work with my file by using If condition instead of sum product and update you.

4. Registered User
Join Date
Oct 2013
Posts
165
Hi Everybody,

So that I will try to create my formula.

5. Registered User
Join Date
Oct 2013
Posts
165
Hi Everybody,