Application.worksheetfunction.sumifs on large dataset

Hi,

I am trying to find a more efficient way of doing something where i am using

application.worksheetfunction.sumifs

in my macro.

In the excel file there is a summary sheet and in column-A is a list of LotIds.
A second sheet "raw_data" contains the data ranges used in the vba code.

based on the lotids in column-A of the summary sheet i calc a sum as follows:

Please Login or Register  to view this content.

output_subj_id_array is just an array of the lotids on summary sheet column - A

the ranges listed are ranges found on the raw_data worksheet


the current code takes about 43seconds........

any idea how to make more efficient...........should i use scripting dictionary........if so how to do this?

Hi Welchs,
I have two options

Please Login or Register  to view this content.

Please Login or Register  to view this content.

hi nilem,

thanks for responding.........i will review your answers. sorry for not getting back to you until now......its saturday here and had to go to kids soccer game.

thanks.

nilem,

the scripting.dictionary example works great!!!!! and very very fast.......thanks.