Hi,

I'm using a formula (it gets added via VBA as part of a larger macro into a template every month), I tried it manually and get the same results or the system hangs.
Main table is 27 columns and 140 500 rows.

The formula has to count UNIQUE values 'Site' when 2 criteria are met 'Region' and 'Format'. It works on smaller data sets and calculates correctly but then it gives me 1 for all, on larger data sets.

This is the formula I used: 
Please Login or Register  to view this content.
When I reduce the data row amount it works, but this is a macro that runs on dynamic data every month.

I then read online that COUNTA doesn't work too well with large data sets so I switched to using ROWS formula. This is the current formula, but now it just gives me 0 for all:
Please Login or Register  to view this content.
I reduced the data amount again and then the formula works...But as mentioned before this is a macro that runs on dynamic data every month, so sometimes the data has many more rows.

Any idea what to do OR is there another formula option that can count Unique values with multiple criteria on large data sets?