VBA code to automate calculation in column L:S

Looking for VBA to automate calculation in column L:S due to large datasets. Already have a formula to look through column A and column B to do the sum of each unique firm. The sum formula looks through column A and column B and then perform the summation for each firm. The setback with this sum function is that the datasets is 400,000 and I have to split the data into 7,000 datasets for my workbook not to freeze or slow down - takes hours to get the results with the formula.

Column L formula: = SUMIFS($D$2:$D$400000,$A$2:$A$400000,A2,$C$2:$C$400000,C2)

Column M formula: = SUMIFS(E$2:E$400000,$A$2:$A$400000,$A2,$C$2:$C$400000,$C2)

Column N formula: = SUMIFS(F$2:F$400000,$A$2:$A$400000,$A2,$C$2:$C$400000,$C2)

Column O formula: = SUMIFS(G$2:G$400000,$A$2:$A$400000,$A2,$C$2:$C$400000,$C2)

Column P formula: = SUMIFS(H$2:H$400000,$A$2:$A$400000,$A2,$C$2:$C$400000,$C2)

Column Q formula: = SUMIFS(I$2:I$400000,$A$2:$A$400000,$A2,$C$2:$C$400000,$C2)

Column R formula: = SUMIFS(J$2:J$400000,$A$2:$A$400000,$A2,$C$2:$C$400000,$C2)

Column S formula: = SUMIFS(K$2:K$400000,$A$2:$A$400000,$A2,$C$2:$C$400000,$C2)

Thanks
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

jindon: jindon provided this solution for a similar request and it worked.
Similar request:Column J formula: =SUMIFS($D$2:$D$125,$A$2:$A$125,A2,$B$2:$B$125,B2)

Column K formula: =SUMIFS($E$2:$E$125,$A$2:$A$125,A2,$B$2:$B$125,B2)

Column L formula: =SUMIFS($F$2:$F$125,$A$2:$A$125,A2,$B$2:$B$125,B2)

Column M formula: =SUMIFS($G$2:$G$125,$A$2:$A$125,A2,$B$2:$B$125,B2)

Column N formula: =SUMIFS($H$2:$H$125,$A$2:$A$125,A2,$B$2:$B$125,B2)

Column O formula: =SUMIFS($I$2:$I$125,$A$2:$A$125,A2,$B$2:$B$125,B2)

Please Login or Register  to view this content.

Thanks

Originally Posted by bjnockle

... to look through column A and column B to do the sum of each unique firm ... the datasets is 400,000 ...

If you care about the sums calculation for only unique items (firms) and with so much data (400,000 and more)
it is better to use the simple SQL in vbe code, e.g.:
(however, it is important that the data in the columns have the same format)

Please Login or Register  to view this content.