Dynamic VBA Sumif on Large Data - Very slow

Hi all,

I have put a sumif in VBA and currently it is too slow for what i need it to do.

Data sheet

Date	Category	AM	PM
01/01/2019	Red	20	50
02/01/2019	Blue	10	17
02/01/2019	Red	5	7

SumIF summary table:

Date		AM	PM
01/01/2019		20	50
02/01/2019		15	24
03/01/2019		0	0

The above tables are simplified however the data is similar - the data is one sheet and the summary sheet is in a similar format - the columns will be identifical so might help with looping the data. The table structure is almost identical in the data sheet and the summary sheet in terms of columns
Here is the code that i have so far but is very slow. I loop every column and row in the summary sheet and put a sumif in their, as the column changes in the summary sheet so does the data sheet as the column structure is the same.

Basically in the summary sheet i want sumif based on the date and as the loop changes column in the summary sheet, so does the sum range in the data sheet as the columns are the same so i dont need to check the criteria in the columns AM/PM

Please Login or Register  to view this content.

Any help appreciated

You can use Pivot tables, or use sumif function in VBA instead of looping. Could you attach a sample which mimcks your real data?

Hi,

Example file attached of data layout

Thanks

Please Login or Register  to view this content.