Customized Sumproduct Function in Excel VBA

[velu130486]

Dear Seniors,

I am new user to VBA. I had created a Array sum product formula with multiple conditions. 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, DEPT Value in D1

{=SUMPRODUCT((DEPT=D1)*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. I am browsing the internet for last 2 weeks but I could not crack the code. Please help me to sort out this issue and this will help me a lot.

My Inputs will be CPYFORIFD, CPYFORIFA, CPYFORIFR (Dates), CPYWF (Double) and DEPT (String)




Thanks and Regards
R. Vadivelan

 

[velu130486]

Hi Andrew,

Sorry It was my assumption. I had declared it as a range since I need to Sumproduct the Progress and WFAddr in order to get my final Result. Could you please advice me whether I need to declare the Progress First, If it is How to define the same?

And the MsgBox is not working and my result still remains the same.

Thanks in Advance for your help
Regards
R. Vadivelan

 

[Andrew Poulsom]

What do you mean by "the MsgBox is not working"?

 

[velu130486]

Hi Andrew,

I had added the following code in my Function, however when I call this function, It does not show any message box

MsgBox "=SUMPRODUCT(" & Progress & ")*(" & WFAddr & "))"

Thanks in Advance for your help
Regards
R. Vadivelan

 

[Andrew Poulsom]

In that case the previous line is failing.

 

[velu130486]

Hi Andrew,

Can you help me to correct the code so that I can perform the calculation in 2 steps?

Function Overall_Progress2(CutoffDate As Range, IFRDates As Range, IFADates As Range, IFDDates As Range, WF As Range)     Dim Cutoff As String, IFR As String, IFA As String, IFD As String, WFAddr As String     Dim Progress As Range     Cutoff = "'" & CutoffDate.Parent.Name & "'!" & CutoffDate.Address(False, False)     IFR = "'" & IFRDates.Parent.Name & "'!" & IFRDates.Address(False, False)     IFA = "'" & IFADates.Parent.Name & "'!" & IFADates.Address(False, False)     IFD = "'" & IFDDates.Parent.Name & "'!" & IFDDates.Address(False, False)     WFAddr = "'" & WF.Parent.Name & "'!" & WF.Address(False, False)     Progress = Application.Evaluate("=IF((" & IFD & ">0)*(" & IFD & "<=" & Cutoff & ")),1,IF(((" & IFA & ">0)*(" & IFA & "<=" & Cutoff & ")),0.8,IF(((" & IFR & ">0)*(" & IFR & "<=" & Cutoff & ")),0.6,0)))")     Overall_Progress2 = Application.Evaluate("=SUMPRODUCT(" & Progress & ")*(" & WFAddr & "))") End Function</pre>

Otherwise I will go with the code provided by you earlier which provides me the result using SUM PRODUCT function.

Thanks in Advance for your help
Regards
R. Vadivelan

 

[Andrew Poulsom]

Why do you need to do it in 2 steps? I'm not sure it's possible.

 

[velu130486]

Hi Andrew,

Ok its not a problem. As you said .Evaluate having some limitation I wanted to shorten the code that's it. But Now I have a different problem.

Function Overall_Progress(CutoffDate As Range, IFRDates As Range, IFADates As Range, IFDDates As Range, WF As Range)
    Dim Cutoff As String, IFR As String, IFA As String, IFD As String, WFAddr As String
    Cutoff = "'" & CutoffDate.Parent.Name & "'!" & CutoffDate.Address(False, False)
    IFR = "'" & IFRDates.Parent.Name & "'!" & IFRDates.Address(False, False)
    IFA = "'" & IFADates.Parent.Name & "'!" & IFADates.Address(False, False)
    IFD = "'" & IFDDates.Parent.Name & "'!" & IFDDates.Address(False, False)
    WFAddr = "'" & WF.Parent.Name & "'!" & WF.Address(False, False)
    Overall_Progress = Application.Evaluate("=SUMPRODUCT(IF(((" & IFD & ">0)*(" & IFD & "<=" & Cutoff & ")),1,IF(((" & IFA & ">0)*(" & IFA & "<=" & Cutoff & ")),0.8,IF(((" & IFR & ">0)*(" & IFR & "<=" & Cutoff & ")),0.6,0)))*(" & WFAddr & "))")
End Function

In the Line, I would like to add One more condition to check (i.e) IFD = NA. Based on the recorded Macro I edited the formula as follows but it returns error.

Application.Evaluate("=SUMPRODUCT(IF(((" & IFD & ">0)+(" & IFD & "=NA)*(" & IFD & "<=" & Cutoff & "))

I had tried putting Double quotes also but no results.

Thanks in Advance for your help
Regards
R. Vadivelan

 

[Andrew Poulsom]

What would the formula be if you entered it in a cell? This is getting very complicated and I really don't see any benefit from using a UDF rather than a formula. Also I don't see the benefit of using a megaformula rather than several formulas and summing the results.

 

[velu130486]

Hi Andrew,

The following is the formula I entered in the cell.

=SUMPRODUCT((IF(((IFD1ST>0)+(IFD1ST="NA")*(IFD1ST<=C$1)),1,IF(((IFA1ST>0)+(IFA1ST="NA")*(IFA1ST<=C$1)),0.8,IF(((IFR1ST>0)+(IFR1ST="NA")*(IFR1ST<=C$1)),0.6,0))))*(CPYWF))

Sometimes there is a chance of getting a progress without issuing the Document, that's why I need to Check for "NA" and C1 refers the Cut Off Date. This is the only condition need to be added after this I will close out this issue.

Thanks in Advance for your help
Regards
R. Vadivelan

 

[Andrew Poulsom]

Record a macro while entering that formula and you will get the syntax for Evaluate.