VBA code required to SUM the value depends on multiple criteria

[Anbuselvam]

Hi

In the below link excel sheet has dropdown list in the cell P1.

In the cell EL1 will shows the results depends on the P1 selection

For that, I used If, sumproduct and index match formulas in the cell EL1 as below.

--

=IF(P1=EN2,(SUMPRODUCT((J8:J30495<=EN1)*(J8:J30495>=EO1)*(G8:G30495="Sales")*CF8:CF30495)),

(IF(P1=EN3,(SUMPRODUCT((J8:J30495<=EN1)*(J8:J30495>=EO1)*(G8:G30495="Sales")*T8:T30495)),

(IF(P1=EN5,SUMPRODUCT((J8:J30495<=EN1)*(J8:J30495>=EO1)*(G8:G30495="Sales")*(BY8:BY30495)),

IF(P1=EN6,(SUMPRODUCT(CG4:EF4,INDEX('RM Price'!$B$2:$BA$239,MATCH('Master Data'!$EN$1,'RM Price'!$A$2:$A$239),))),

(IF(P1=EN7,(SUMPRODUCT((B8:B20496<=EN1)*CE8:CE20496))-(SUMPRODUCT((J8:J20496<=EN1)*BZ8:BZ20496)),

(IF(P1=EN4,(SUMPRODUCT((B8:B20496<=EN1)*CF8:CF20496))-(SUMPRODUCT((J8:J20496<=EN1)*CA8:CA20496)),0))))))))))

--

I would like to convert those formulas with VBA code. Mainly the highlighted with bold formulas I want to write the VBA code.

https://www.dropbox.com/s/zmdi1kd2q0nk0xg/Mr Excel.xlsb?dl=0


I hope someone will help in this regards

Thanks in advance

Sincerely Yours
Anbuselvam K

 

[Anbuselvam]

Can anyone provide the code?


I hope someone will guide me to write the VBA code without showing formulas in the result cell to get the below answer (similar to my previous post but bit different)

Column A8:A1000 I have production date (1-1-2015 to 31-12-2017)

Column B8:B1000 I have Sales date (1-1-2015 to 31-12-2017)

Column C8:C1000 I have Production Quantity

Column D8:D1000 I have Sales Quantity

In cell E2 Start date is 3-3-2015

In cell E3 End date is 9-9-2017

In cell E4 i need a total of column C, in between the D2 and D3 dates of Column A (Production quantity between those dates)

In cell E5 i need a total of column D, in between the D2 and D3 dates of Column B (Sales quantity between those dates)

https://www.dropbox.com/s/ifdak1vx82xgb5w/Sum Between two dates.xlsx?dl=0