Excel - A Count if macro based on multiple criteria

December 2016


Could someone please help me out with a count-if macro based on multiple criteria. I have attached a sample file: here

I need a count of word "False" on different ranges starts from column c1 to goes on. The macro should give me the count of different range in the columns C:C and stop until there is none in next cell.

The ranges of the data might change from day to day. so i want the macro to count on the ranges which are empty cells in column C and stop if there is nothing in the other cell.


Try this macro:

Sub test()  
Dim r As Range, j As Integer, k As Integer, m As Integer
j = Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Row
k = j
If k = 1 Then Exit Do
If Cells(k, "C").Offset(-2, 0) = "" Then
Set r = Cells(k, "c").Offset(-1, 0)
Set r = Range(Cells(k, "C").Offset(-1, 0), Cells(k, "c").Offset(-1, 0).End(xlUp))
End If
'msgbox r.Address
m = WorksheetFunction.CountIf(r, "false")
'msgbox m
Cells(k, "c") = m
If Cells(k, "c").End(xlUp).Address = "$C$1" Then Exit Sub
k = Cells(k - 1, "c").End(xlUp).Offset(-1, 0).Row
'msgbox k

End Sub

Sub undo()
Dim r As Range, c As Range
Set r = Range(Range("C1"), Cells(Rows.Count, "C").End(xlUp))
For Each c In r
If WorksheetFunction.IsNumber(c) Then c.Clear
Next c
End Sub

Thanks to venkat1926 for this tip on the forum.

Related :

This document entitled « Excel - A Count if macro based on multiple criteria » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.