Help needed to insert SUMPRODUCT formula in Macro

Navsaky17

New Member
Joined
Jan 20, 2014
Messages
3
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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,912
Messages
5,598,828
Members
414,260
Latest member
joishe

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top