Loop for Countif function from a range

Emeric

New Member
Joined
Jul 19, 2017
Messages
27
Hello! ;)
I have 2 excel sheet :
- “Total Report”
- “Statistics”
In the sheet “Statistics” I have one column “A3:A10” with a text in each cells (e.g. “A3” = “Alerts”).
In the sheet “Total Report” I have a table, and inside I have 3 ranges: "F2:F3000", "J2:J3000" and "N2:N3000". Theses 3 ranges contains cells with similar values that are present in the column “A3:A10” from the sheet “Statistics”. I need a function which is capable to count how many time values from column A3:A10” from the sheet “Statistics” are present in the 3 ranges: "F2:F3000", "J2:J3000" and "N2:N3000" from the sheet “Total Report”.
I would like to do it in an automatic way. I mean the Macro must be able to pick the text from “A3:A10” and search in the 3 ranges "F2:F3000", "J2:J3000" and "N2:N3000" how many time this term is present.
I did a code, it works, but I want to do it more dynamic because if someone add one value to the list “A3:A10”, the Macro must be able to add this text in the calculs (I don’t want to had new lines in my code when I had a new cell in the list “A3:A10”.
Results of the “countif” must be present on the cells next to the list “A3:A10” like this:
- Column B for Task1
- Column C for Task2
- Column D for Task3
- Then SUM B + C + D in Column E
I can’t merge a the 3 ranges "F2:F3000", "J2:J3000" and "N2:N3000" in one with “Application.Union” but VBA doesn’t want to recognize it.
I tried to write a loop, but I don’t succed.
This is the code I write:

Code:
Public Sub countTaskocc()
 
Dim Task1 As Range
Dim Task2 As Range
Dim Task3 As Range
 
Set Task1 = Worksheets("Total Report").Range("F2:F3000")
Set Task2 = Worksheets("Total Report").Range("J2:J3000")
Set Task3 = Worksheets("Total Report").Range("N2:N3000")
 
For Each cell In Task1
If cell.Value = Range("a3").Value Then
Range("b3") = WorksheetFunction.CountIf(Task1, Range("A3"))
ElseIf cell.Value = Range("a4").Value Then
Range("b4") = WorksheetFunction.CountIf(Task1, Range("A4"))
ElseIf cell.Value = Range("a5").Value Then
Range("b5") = WorksheetFunction.CountIf(Task1, Range("A5"))
ElseIf cell.Value = Range("a6").Value Then
Range("b6") = WorksheetFunction.CountIf(Task1, Range("A6"))
ElseIf cell.Value = Range("a7").Value Then
Range("b7") = WorksheetFunction.CountIf(Task1, Range("A7"))
ElseIf cell.Value = Range("a8").Value Then
Range("b8") = WorksheetFunction.CountIf(Task1, Range("A8"))
ElseIf cell.Value = Range("a9").Value Then
Range("b9") = WorksheetFunction.CountIf(Task1, Range("A9"))
ElseIf cell.Value = Range("a10").Value Then
Range("b10") = WorksheetFunction.CountIf(Task1, Range("A10"))
End If
Next
 
For Each cell In Task2
If cell.Value = Range("a3").Value Then
Range("c3") = WorksheetFunction.CountIf(Task2, Range("A3"))
ElseIf cell.Value = Range("a4").Value Then
Range("c4") = WorksheetFunction.CountIf(Task2, Range("A4"))
ElseIf cell.Value = Range("a5").Value Then
Range("c5") = WorksheetFunction.CountIf(Task2, Range("A5"))
ElseIf cell.Value = Range("a6").Value Then
Range("c6") = WorksheetFunction.CountIf(Task2, Range("A6"))
ElseIf cell.Value = Range("a7").Value Then
Range("c7") = WorksheetFunction.CountIf(Task2, Range("A7"))
ElseIf cell.Value = Range("a8").Value Then
Range("c8") = WorksheetFunction.CountIf(Task2, Range("A8"))
ElseIf cell.Value = Range("a9").Value Then
Range("c9") = WorksheetFunction.CountIf(Task1, Range("A9"))
ElseIf cell.Value = Range("a10").Value Then
Range("c10") = WorksheetFunction.CountIf(Task1, Range("A10"))
End If
Next
 
For Each cell In Task3
If cell.Value = Range("a3").Value Then
Range("d3") = WorksheetFunction.CountIf(Task3, Range("A3"))
ElseIf cell.Value = Range("a4").Value Then
Range("d4") = WorksheetFunction.CountIf(Task3, Range("A4"))
ElseIf cell.Value = Range("a5").Value Then
Range("d5") = WorksheetFunction.CountIf(Task3, Range("A5"))
ElseIf cell.Value = Range("a6").Value Then
Range("d6") = WorksheetFunction.CountIf(Task3, Range("A6"))
ElseIf cell.Value = Range("a7").Value Then
Range("d7") = WorksheetFunction.CountIf(Task3, Range("A7"))
ElseIf cell.Value = Range("a8").Value Then
Range("d8") = WorksheetFunction.CountIf(Task3, Range("A8"))
ElseIf cell.Value = Range("a9").Value Then
Range("d9") = WorksheetFunction.CountIf(Task1, Range("A9"))
ElseIf cell.Value = Range("a10").Value Then
Range("d10") = WorksheetFunction.CountIf(Task1, Range("A10"))
End If
Next
 
Range("e3") = WorksheetFunction.Sum(Range("B3") + Range("c3") + Range("d3"))
Range("e4") = WorksheetFunction.Sum(Range("B4") + Range("c4") + Range("d4"))
Range("e5") = WorksheetFunction.Sum(Range("B5") + Range("c5") + Range("d5"))
Range("e6") = WorksheetFunction.Sum(Range("B6") + Range("c6") + Range("d6"))
Range("e7") = WorksheetFunction.Sum(Range("B7") + Range("c7") + Range("d7"))
Range("e8") = WorksheetFunction.Sum(Range("B8") + Range("c8") + Range("d8"))
Range("e9") = WorksheetFunction.Sum(Range("B9") + Range("c9") + Range("d9"))
Range("e10") = WorksheetFunction.Sum(Range("B10") + Range("c10") + Range("d10"))
 
Range("B1").EntireColumn.Delete
Range("b1").EntireColumn.Delete
Range("b1").EntireColumn.Delete
 
End Sub


I hope that i'm clear. Thank you for your consideration. Sorry for my English.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hello Emeric,

I've made your code dynamic. Just adjust the ranges and sheetnames.

Code:
Public Sub countTaskocc()
 
Dim Task1 As Range
Dim Task2 As Range
Dim Task3 As Range
Dim lr As Long
  
Set Task1 = Worksheets("Sheet2").Range("A2:A3000")
Set Task2 = Worksheets("Sheet2").Range("B2:B3000")
Set Task3 = Worksheets("Sheet2").Range("C2:C3000")
 
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 1 Step -1
    For Each cell In Task1
        If cell.Value = Range("A" & i).Value Then
        Range("b" & i) = WorksheetFunction.CountIf(Task1, Range("A" & i))
        End If
    Next
Next

For i = lr To 1 Step -1
    For Each cell In Task2
        If cell.Value = Range("A" & i).Value Then
        Range("c" & i) = WorksheetFunction.CountIf(Task2, Range("A" & i))
        End If
    Next
Next

For i = lr To 1 Step -1
    For Each cell In Task3
        If cell.Value = Range("A" & i).Value Then
        Range("d" & i) = WorksheetFunction.CountIf(Task3, Range("A" & i))
        End If
    Next
Next

For i = lr To 1 Step -1
Range("E" & i) = WorksheetFunction.Sum(Range("B" & i) + Range("c" & i) + Range("d" & i))
Next

 
End Sub
 
Upvote 0
Hello MrJoosten.

Thank you very much for your answer! I saved me.

However, I have one question: How should I adjust the ranges ? Can you please give me one example ?
 
Upvote 0
Hello Emeric,

Change:
Code:
Set Task1 = Worksheets("Sheet2").Range("A2:A3000")
Set Task2 = Worksheets("Sheet2").Range("B2:B3000")
Set Task3 = Worksheets("Sheet2").Range("C2:C3000")|

into:
Code:
Set Task1 = Worksheets("Total Report").Range("F2:F3000")
Set Task2 = Worksheets("Total Report").Range("J2:J3000")
Set Task3 = Worksheets("Total Report").Range("N2:N3000")

Then see what it does. :)
 
Upvote 0
Oooh. I'm such an idiot.
Thank you so much MrJoosten, it works perfectly. You are the best. :cool:
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top