Excel - A Count if macro based on multiple criteria

December 2016





Issue

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.

Solution

Try this macro:

Sub test()  
Dim r As Range, j As Integer, k As Integer, m As Integer
Worksheets("sheet2").Activate
j = Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Row
k = j
Do
If k = 1 Then Exit Do
If Cells(k, "C").Offset(-2, 0) = "" Then
Set r = Cells(k, "c").Offset(-1, 0)
Else
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
Loop

End Sub


Sub undo()
Dim r As Range, c As Range
Worksheets("sheet2").Activate
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.