Excel: Customized Sumproduct Function in Excel VBA

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

This question generated 50 answers. To proceed to the answers, click here.

This thread is current as of July 27, 2014.

For more resources for Microsoft Excel