 # Count if macro on multiple criteria [Solved/Closed]

-
Wasem -
Hello,

Could someone please help me out with a count-if macro on multiple criteria. I have attached a sample file in the below link

http://www.speedyshare.com/files/26835568/sample_file.xlsx

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.

Am working on this data from past one month just stuck up with the count.

It would be great if someone help me out !!!

## 2 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
try this macro "test". the macro undo is to undo the result of the macro test

```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
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
```
Hi Venkat,

Thank you very much for your help !!

I am not very much familiar about the above codes !!

Again thank you very much !!
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
which is your operative sheet. I have taken as sheet2. if it is some other sheet then correct the statement
Worksheets("sheet2").Activate

in BOTH the macros.

I have given again the macro with some explantions and msgbox(s). you will get a pop up box giving you the results. if cick ok the macro will resume. this will give you an idea what the macro is doing. Afer you are convinced you can convert these msgbox(s) into comments by typing single spostrophe(') at the beginning of the statements.

As you have said "i want the macro " you know how and where to park the macros and how to un them.

```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
'j is the last item in the column C
MsgBox j
k = j
'loops through the cells in c starting from the last cell upto cell C2
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
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
```
Thanks Venkat !! Thanks for your great help !!
Recommended

DON'T MISS

TRENDING GAMES & APPS