Quicker formulas for counting unique values

[mcfclax]

Hi

I have the following macro (as part of a much bigger set of code that adds some formulas to to cells to return the total number of unique values. The problem is, this part of the macro takes over 10 minutes to run. Is there a quicker way of doing this or am I going to have to just wait it out each time.

The columns B and/or H is a formula. Column W is an string value.

FYI the last row it is counting to is between 4000 and 30000 lines in each of the formulas

 Dim IRMatRow As Integer
    Dim IRMisRow As Integer
    Dim UKMatRow As Integer
    Dim UKMisRow As Integer
    IRMatRow = Worksheets("Matching Stats").Cells(Rows.Count, "H").End(xlUp).Row
    IRMisRow = Worksheets("MIS Report").Cells(Rows.Count, "B").End(xlUp).Row
    UKMatRow = Worksheets("Matching Stats UK").Cells(Rows.Count, "H").End(xlUp).Row
    UKMisRow = Worksheets("MIS Report UK").Cells(Rows.Count, "B").End(xlUp).Row
    Range("C15").Value = "IR"
    Range("C16").FormulaArray = "=SUM(IF(""System""='Matching Stats'!$W$3:$W$" & IRMatRow & ", 1/(COUNTIFS('Matching Stats'!$W$3:$W$" & IRMatRow & ", ""System"",'Matching Stats'!$H$3:$H$" & IRMatRow & ", 'Matching Stats'!$H$3:$H$" & IRMatRow & ")), 0))-1"
    Range("C17").FormulaArray = "=SUM(IF(""System""<>'Matching Stats'!$W$3:$W$" & IRMatRow & ", 1/(COUNTIFS('Matching Stats'!$W$3:$W$" & IRMatRow & ", ""<>System"",'Matching Stats'!$H$3:$H$" & IRMatRow & ", 'Matching Stats'!$H$3:$H$" & IRMatRow & ")), 0))-1"
    Range("C18").FormulaArray = "=SUM(1/COUNTIF('Matching Stats'!$H$3:$H$" & IRMatRow & ",'Matching Stats'!$H$3:$H" & IRMatRow & "))-1"
    Range("C20").FormulaArray = "=SUM(1/COUNTIF('MIS Report'!$B$2:$HB" & IRMisRow & ",'MIS Report'!$B$2:$B$" & IRMisRow & "))-1"
    Range("D15").Value = "UK"
    Range("D16").FormulaArray = "=SUM(IF(""System""='Matching Stats UK'!$W$3:$W$" & UKMatRow & ", 1/(COUNTIFS('Matching Stats UK'!$W$3:$W$" & UKMatRow & ", ""System"",'Matching Stats UK'!$H$3:$H$" & UKMatRow & ", 'Matching Stats UK'!$H$3:$H$" & UKMatRow & ")), 0))-1"
    Range("D17").FormulaArray = "=SUM(IF(""System""<>'Matching Stats UK'!$W$3:$W$" & UKMatRow & ", 1/(COUNTIFS('Matching Stats UK'!$W$3:$W$" & UKMatRow & ", ""<>System"",'Matching Stats UK'!$H$3:$H$" & UKMatRow & ", 'Matching Stats UK'!$H$3:$H$" & UKMatRow & ")), 0))-1"
    Range("D18").FormulaArray = "=SUM(1/COUNTIF('Matching Stats UK'!$H$3:$H$" & UKMatRow & ",'Matching Stats UK'!$H$3:$H" & UKMatRow & "))-1"
    Range("D20").FormulaArray = "=SUM(1/COUNTIF('MIS Report UK'!$B$3:$B$" & UKMisRow & ",'MIS Report UK'!$B$2:$B$" & UKMisRow & "))-1"

 

[mcfclax]

Essentially the above code should add the below formulas to various cells

{=SUM(IF("System"='Matching Stats'!$W$1:$W$3, 1/(COUNTIFS('Matching Stats'!$W$1:$W$3, "System",'Matching Stats'!$H$1:$H$3, 'Matching Stats'!$H$1:$H$3)), 0))-1}
{=SUM(1/COUNTIF('Matching Stats'!$H$1:$H$3,'Matching Stats'!$H1:$H$3))-1}

It does this, but takes an age to post the code.

 

[mcfclax]

Cheeky bump on this to see if anyone has an answer. Thanks