CountIfs function to call in formula
Posted on 2014-11-06
I have a function for a countif. It is:
Function myCountIf(rng As Range, criteria) As Long
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Metrics" And ws.Name <> "TFSData" Then
myCountIf = myCountIf + WorksheetFunction.CountIf(ws.Range(rng.Address), criteria)
I have a spreadsheet that has a formula in one cell. I call this function by: =myCountIf(B:B,A15). This will go to every sheet in the workbook that is not in the If clause, and get the values that match the value in A15. I need a countifs function. I want the formula to go through each sheet not listed in the If clause, look for the value in column B that matches the A15 cell value of the current sheet And then count the value in the H column of that row.
I tried changing the function myCountifs line in the following way but it did not work:
myCountIfs = myCountIfs + WorksheetFunction.CountIfs(ws.Range(rng.Address), criteria, ws.Range(rng.Address), criteria)
I called it by writing: -myCountIfs(B:B,A15,H:H,"1")
I get the #VALUE! in the cell. Anyone know what is wrong with it?