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,

I already recorded a Macro and edit the Code as follows, However my UDF doesn't return the result as earlier.

Overall_Progress = Application.Evaluate("=SUMPRODUCT(IF(((" & IFD & ">0)+(" & IFD & "=""NA"")*(" & IFD & "<=" & Cutoff & ")),1,IF(((" & IFA & ">0)+(" & IFA & "=""NA"")*(" & IFA & "<=" & Cutoff & ")),0.8,IF(((" & IFR & ">0)+(" & IFR & "=""NA"")*(" & IFR & "<=" & Cutoff & ")),0.6,0)))*(" & WFAddr & "))")

Could you please help me to identify what is wrong in the above Code, so that I can close out this issue.

The Following is the Code I got recorded in Macro

Selection.FormulaArray = _
        "=SUMPRODUCT(IF(((IFD1ST>0)+(IFD1ST=""NA"")*(IFD1ST<=R1C)),1,IF(((IFA1ST>0)+(IFA1ST=""NA"")*(IFA1ST<=R1C)),0.8,IF(((IFR1ST>0)+(IFR1ST=""NA"")*(IFR1ST<=R1C)),0.6,0))))*(CPYWF))"

Thanks in Advance for your help
Regards
R. Vadivelan

 

[Andrew Poulsom]

If you put this before that line what does it return?

MsgBox Len("=SUMPRODUCT(IF(((" & IFD & ">0)+(" & IFD & "=""NA"")*(" & IFD & "<=" & Cutoff & ")),1,IF(((" & IFA & ">0)+(" & IFA & "=""NA"")*(" & IFA & "<=" & Cutoff & ")),0.8,IF(((" & IFR & ">0)+(" & IFR & "=""NA"")*(" & IFR & "<=" & Cutoff & ")),0.6,0)))*(" & WFAddr & "))")

 

[velu130486]

Hi Andrew,

It returns 292, So I understood from your previous post .Evaluate will not work for more than 255 characters. So Is there anything you can suggest to make this formula working.

Otherwise I will store the formula in Name manager and use the same for my project. But my query is If I use the Name manager, is it possible to select the Cut Off Date dynamically?

Thanks in Advance for your help
Regards
R. Vadivelan

 

[Andrew Poulsom]

Isn't NA greater than zero (as is any text)? That being he case you don't need to test for it.

 

[velu130486]

Hi Andrew,

NA is a Text. When the document is not issued, then it will be updated as NA. But for calculation it need to be considered. Thats why I need to check IFD contains NA then progress as to be considered as 1, same for IFA 0.8, IFR 0.6.

Thanks in Advance for your help
Regards
R. Vadivelan

 

[Andrew Poulsom]

For me:

="NA">0

returns TRUE. So cells containing NA will pass the test for greater than zero won't they?

 

[velu130486]

Hi Andrew,

="NA">0 passes true for me too. However the UDF returns the wrong value. If I replace the NA with some date then I gets the correct value. So the problem is in the UDF, NA don't pass the test.

Thanks in Advance for your help
Regards
R. Vadivelan

 

[Andrew Poulsom]

Are you sure that your formula is doing what you think? What is the correct answer here?

Excel 2010
	A	B	C
1	1	Cutoff	3
2	2	With NA	4
3	0	Without NA	2
4	NA
5	5
Sheet1

Cell Formulas
Range	Formula
C2	{=SUM(IF(((A1:A5>0)+(A1:A5="NA")*(A1:A5<=C$1)),1))}
C3	{=SUM(IF(((A1:A5>0)*(A1:A5<=C$1)),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[velu130486]

Hi Andrew,

The correct answer is 3. However the formula returns 4 and 2.

I used the following formula and it gives me the correct result

=SUMPRODUCT(($A$1:$A$5<=$C$1)*($A$1:$A$5>0)+($A$1:$A$5="NA"))

My Objective is to check for NA in the UDF in order to close this issue.

Thanks in Advance for your help
Regards
R. Vadivelan

 

[velu130486]

Hi Andrew,

I found the difficulties in the Above formula using NA. So I had instructed my Team to not put NA in the Actual data. So the code provided by you earlier is works fine and many thanks for that.

Function ExecSumProduct(DEPT As Range, CutoffDate As Range)     With Application.Caller.Parent         ExecSumProduct = .Evaluate("=SUMPRODUCT((DEPT=" & DEPT.Address & ")*IF(((CPYFORIFD>0)*(CPYFORIFD<=" & CutoffDate.Address & ")),1,IF(((CPYFORIFA>0)*(CPYFORIFA<=" & CutoffDate.Address & ")),0.8,IF(((CPYFORIFR>0)*(CPYFORIFR<=" & CutoffDate.Address & ")),0.6,0)))*(CPYWF))")     End With End Function</pre>

With reference to the above code I am trying to create the UDF with help of Name managers and Minimum Inputs and I converted 2 formulas already. If there I face any difficulties I post you the same in new thread. Since my problem is solved I will close this thread.

Thanks and Regards
R. Vadivelan