Help needed to insert SUMPRODUCT formula in Macro

[Navsaky17]

Hi Folks,

I am using a SUMPRODUCT formula in my excel. Now i would like to know whether can i hardcode the sumproduct formula in the macro?

This is the Sumproduct formula am currently using. This formula calculates the sum of entries entered for the month january with few conditions (Col c= "Fund set up" & Col E = "Input). All these data's are fetched from another excel (Sakthi.xlsm) saved in different location in my local drive.

=SUMPRODUCT(--(MONTH('C:\Documents and Settings\user.BNP\My Documents\Downloads\[Sakthi.xlsm]MIS'!$B$2:$B$9000)=1)*('C:\Documents and Settings\user.BNP\My Documents\Downloads\[Sakthi.xlsm]MIS'!$C$2:$C$9000="Fund Set up")*('C:\Documents and Settings\user.BNP\My Documents\Downloads\[Sakthi.xlsm]MIS'!$D$2:$D$9000)*('C:\Documents and Settings\user.BNP\My Documents\Downloads\[Sakthi.xlsm]MIS'!$E$2:$E$9000="Input"))

Kindly assist me to put this formula in the macro.

I need to do this in macro because i hv to write the same formula for all 12 months (Jan - Dec) and for various activities. Also totally there are 30 members in my team. So if i write the formula for all the 30 members then the excel size will be huge and the performance will be slow.

hence kindly help me to do this in macros

Thanks a ton for your help mates!

Regards,
Nav

 

[Fazza]

hi, Nav

It sounds like the slow sumproduct formula has prompted the request to use VBA. I suspect the best solution is to not use array formulas. How about using a different approach?

A pivot table may do what you want - quickly & simply without VBA.

Is that of interest to you? If so, please describe the data set up & the result you want. Some (dummy) sample data would help.

regards