Need alternative to ~13,00,000 countifs formulas

[Samadhan Gaikwad]

Hi All,

I have macro that is adding countifs formula across 450 columns and 3000 rows. Being around 13,00,000 formulas, it's taking too much time(I do not know how much it's taking since I interrupted it in between). Any alternative in vba to accomplish this work.
I am attaching spreadsheet with few data, macro and result.

 

[Hui]

2 considerations

Firstly the way the code is written it copies PartData, Column B and uses it as a Header in Main
But there are duplicate Test values in column B, hence replication of formula that are already there ?

Have you considered using a Pivot Table
It filters and Counts automagically and handles larger data sets better than formula

 

[Samadhan Gaikwad]

Really I didn't think of this. Thank you so much!