Running VBA macro to add COUNTIFS

[jack29]

Hi ,

I have multiple workbooks with 100's to 200's sheets in which I want to count number of entries of A2:A10000 range has, if the cell C2:C10000 has some value then only count should be considered else Not to be considered. This value should be stored in V2 cell of each sheet
Similarly, I want in cell W2, of each sheet I want to count number of entries of A2:A10000 range has, if the cell C2:C10000 has some No-Value then only count should be considered else Not to be considered.

Also, if possible in first sheet, I wanted consolidated grand total of cells value of V2 and W2 across all the sheets.

I tried below VBA code with no luck it prints 0 under V2 and W2 cells. any help is much appreciated.

Sub test()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

With ws
ws.Range("V1").Value = "Rules with Action"
ws.Range("W1").Value = "Rules without Action"
ws.Range("V2") = WorksheetFunction.CountIfs(Range("F2:F5000"), " <> ", Range("M2:M5000"), " <> ")
ws.Range("W2") = WorksheetFunction.CountIfs(Range("F2:F5000"), " <> ", Range("M2:M5000"), "")
End With

Next ws

End Sub

 

[Max1616]

Try this:

Sub test()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

With ws
ws.Range("V1").Value = "Rules with Action"
ws.Range("W1").Value = "Rules without Action"
ws.Range("V2") = WorksheetFunction.CountIfs(ws.Range("F2:F5000"), " <> ", ws.Range("M2:M5000"), " <> ")
ws.Range("W2") = WorksheetFunction.CountIfs(ws.Range("F2:F5000"), " <> ", ws.Range("M2:M5000"), "")
End With

Next ws

End Sub

You need to reference the ws in the worksheetfunction too.

 

[jack29]

Hi @Max1616 , I have tried this still it displays zero. could there any other issue. I have attached screenshots.

 

[Max1616]

ah, I think I figured out the issue: It looks like there's spaces before & after the inequalities causing the error. This is working for me when I test it in Excel:

Sub test()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

With ws
ws.Range("V1").Value = "Rules with Action"
ws.Range("W1").Value = "Rules without Action"
ws.Range("V2") = WorksheetFunction.CountIfs(ws.Range("F2:F5000"), "<>", ws.Range("M2:M5000"), "<>")
ws.Range("W2") = WorksheetFunction.CountIfs(ws.Range("F2:F5000"), "<>", ws.Range("M2:M5000"), "=")
End With

Next ws

End Sub

 

[jack29]

It's working now, that space was culprit. Even without addition of = (equal to ) sign it's working now, would that equal to necessary or we can leave it blank?

Sub test()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

With ws
ws.Range("V1").Value = "Rules with Action"
ws.Range("W1").Value = "Rules without Action"
ws.Range("V2") = WorksheetFunction.CountIfs(ws.Range("F2:F5000"), "<>", ws.Range("M2:M5000"), "<>")
ws.Range("W2") = WorksheetFunction.CountIfs(ws.Range("F2:F5000"), "<>", ws.Range("M2:M5000"), "")

'ws.Range("W2") = WorksheetFunction.CountIfs(ws.Range("F2:F5000"), "<>", ws.Range("M2:M5000"), "=")

End With

Next ws

End Sub

Thank you @Max1616

 

[Max1616]

If it's working without the "=" then you can remove it.