Looping Countifs with Excel Formulas

[Mysore]

Hi All

Currently I'm trying to understand a complex model (Run with VBA) by recreating using normal excel function and then subsequently intend to re-develop the same Using PowerPivot data model as the existing model takes upto 10 hours to run depending on the amount of data selected. I have almost cracked the code and validated the outcome with my formulas with the exception of the below piece of code which basically performs a Loop calculation of Distinct Countifs on multiple criteria. I'm trying to write a complex countifs formula with array Criteria Range along with array Criterias in order to achieve the outcome but in vain.

I was wondering if anybody could guide me in achieving the aforesaid formula. Below is part of the VBA code which does what is explained above.

Thanks


For k = 1 To 16


If Cells(i, 432 + k) = 1 And Cells(2, k + 432) <> "Test" Then


infraCount = WorksheetFunction.CountIfs(rnpWS.Columns(1), Cells(i, 1), rnpWS.Columns(12), Cells(2, 415 + k), rnpWS.Columns(11), "Tower") _
+ WorksheetFunction.CountIfs(rnpWS.Columns(1), Cells(i, 1), rnpWS.Columns(12), Cells(2, 415 + k), rnpWS.Columns(11), "Hut")

infraDesignCost = Range("InfraDesignCost") * (1 + Range("LabourIndRate")) ^ (j - 1)


' InfraOwner Design cost
If infraCount > 0 Then

Cells(i, j + 215) = Cells(i, j + 215) + infraDesignCost ‘ Value keeps incrementing
Sheets("Additions").Cells(375, 4 + j) = Sheets("Additions").Cells(375, 4 + j) + infraDesignCost


refRow = WorksheetFunction.Match(Cells(2, k + 432), Range("Agencies"), 0)
Sheets("Additions").Cells(375 + refRow, j + 4) = Sheets("Additions").Cells(375 + refRow, j + 4) + infraDesignCost

'incidental Cost
Sheets("Additions").Cells(413, j + 4) = Sheets("Additions").Cells(413, j + 4) + Range("IncidentalCost")
Sheets("Additions").Cells(413 + refRow, j + 4) = Sheets("Additions").Cells(413 + refRow, j + 4) + Range("IncidentalCost")

End If