VBA WorksheetFunction - countifs using month not working.

[BenGee]

Hi

I have this code which isn't working;

For Each c In Sheets("Support").Range("B2:B13")

c.Value = (Application.WorksheetFunction.Sum(Application.CountIfs(Worksheets("Data").Range("G:G"), Array("6", "7"), Month(Worksheets("Data").Range("B:B")), Month(c.Offset(, -1).Value))) - Application.WorksheetFunction.Sum(Application.CountIfs(Worksheets("Data").Range("G:G"), Array("1", "2", "3"), Month(Worksheets("Data").Range("B:B")), Month(c.Offset(, -1).Value)))) / Application.WorksheetFunction.Sum(Application.CountIfs(Worksheets("Data").Range("G:G"), Array("1", "2", "3", "4", "5", "6", "7"), Month(Worksheets("Data").Range("B:B")), Month(c.Offset(, -1).Value)))

Next

In short, it's the parts which are within the Month parenthesis which provide a run-time error '13':type mismatch... (removing it entirely then runs fine but isn't the result I want). And for some reason, I just can't get my head around it.

In Sheets("Support").Range("B1:B12") - this contains the 1st of each month in a dd/mm/yyyy format.
In Sheets("Data").Range("B:B") - this contains random dates of the month, across multiple months, also in a dd/mm/yyyy format.

As part of the above worksheet function, all I'm trying to do is include the parameter to 'countif', if the month within each range is the same... What am I missing? Any help or suggestions would be hugely appreciated.

Thank you

 

[Domenic]

Try using the Evauate method instead. In the following example...

1. The code refers to the active workbook. However, if the workbook running the code contains your target worksheets, replace each instance of ActiveWorkbook with ThisWorkbook.
2. The code calculates 3 separate counts before performing the final calculation. However, I've done the first count, you'll need to do the same for the other 2 counts.
3. The Evaluate method has a 255 character limit. Exceeding this limit will cause an error.

    With ActiveWorkbook.Worksheets("Data")
   
        Dim LastRow As Long
        LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

        Dim RangeB As Range
        Set RangeB = .Range("B1:B" & LastRow)
       
        Dim RangeG As Range
        Set RangeG = .Range("G1:G" & LastRow)
       
    End With
   
   
    Dim c As Range
    Dim count1 As Long
    Dim count2 As Long
    Dim count3 As Long
   
    For Each c In ActiveWorkbook.Sheets("Support").Range("B2:B13")
   
        count1 = Evaluate("SUM(IF(ISNUMBER(MATCH(" & RangeG.Address(, , , True) & ",{6,7},0)),IF(MONTH(" & RangeB.Address(, , , True) & ")=MONTH(" & c.Offset(, -1).Value & "),1)))")
        count2 = Evaluate(" . . . ")
        count3 = Evaluate(" . . . ")
       
        c.Value = (count1 - count2) / count3
       
    Next

Hope this helps!