Countif giving me 0

[tt.viquel]

I have changed this countif formula into a macro: but it is giving me 0 instead of 10 does anyone know why??

=COUNTIF(KTPT80T!G:G,"Y")

Sub test()
Dim x As Long
x = Range("G" & Rows.Count).End(xlUp).Row
If x < 100 Then x = 100
Range("Z2") = Application.WorksheetFunction.CountIf(Range("G5:G" & x), "Y")
End Sub

 

[Dartagnan]

"Now i wanted to find out how do i add other countif to the same sub, (6 in total) with different criterias as well e.g. "

Think this will get you started.

Sub newtest()

Dim x As Long
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Select
x = ws.Range("L" & Rows.Count).End(xlUp).Row
If x > 100 Then x = 100
ws.Range("Z4") = Application.WorksheetFunction.CountIf(Range("L1:L" & x), "Y")
x = 0
x = ws.Range("J" & Rows.Count).End(xlUp).Row
If x > 100 Then x = 100
ws.Range("Z5") = Application.WorksheetFunction.CountIf(Range("J1:J" & x), "Y")
Next ws

End Sub

 

[tt.viquel]

Dartagnan, thank you very much, I will use it and add more of my tables

Thanks again

 

[tt.viquel]

hi again,
so i have tested you code on my 2nd (wanted to verify first).
But it keeps running and doesnt stop, so i have to close excel. how can i fix that. is it because it goes on to the next worksheet and i didnt specify when to stop??

Sub newtest()
Dim x As Long
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Select
x = ws.Range("G" & Rows.Count).End(xlUp).Row
If x > 100 Then x = 100
Sheets("Index").Range("Z2") = Application.WorksheetFunction.CountIf(Range("G5:G" & x), "Y")
x = 0
x = ws.Range("H" & Rows.Count).End(xlUp).Row
If x > 100 Then x = 100
sheets("Index").Range("Z10") = Application.WorksheetFunction.CountIf(Range("F5:5" & x), "Y")
Next ws
End Sub

 

[Dartagnan]

Think there is a typo. You say H and then use F and instead of F I see a 5.

In any case the code does need a little work. It keeps running the same code for each sheet.

I know this isn't the most efficient, but it should get you started.

Sub newtest()
Dim x As Long
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Select
If ws.Name = "Sheet1" Then
x = ws.Range("G" & Rows.Count).End(xlUp).Row
If x > 100 Then x = 100
Range("Z2") = Application.WorksheetFunction.CountIf(Range("G5:G" & x), "Y")
x = 0
ElseIf ws.Name = "Sheet2" Then
x = ws.Range("F" & Rows.Count).End(xlUp).Row
If x > 100 Then x = 100
Range("Z10") = Application.WorksheetFunction.CountIf(Range("F5:F" & x), "Y")
x = 0
Else 'Sheet 3 or keep doing elseif for the remaining sheets
x = ws.Range("E" & Rows.Count).End(xlUp).Row
If x > 100 Then x = 100
Range("Z10") = Application.WorksheetFunction.CountIf(Range("E5:E" & x), "Y")
End If
Next ws
End Sub

 

[tt.viquel]

hi,
sorry again but now it doesnt do anything i have modified the code you gave me to add more tables to it..but it's no longer returning anything:s

Sub newtest()
Dim x As Long
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Select
If ws.Name = "KTPT80T" Then
x = ws.Range("G" & Rows.count).End(xlUp).Row
If x > 100 Then x = 100
Range("Z2") = Application.WorksheetFunction.CountIf(Range("G5:G" & x), "Y")
x = 0
ElseIf ws.Name = "KTPTZIT" Then
x = ws.Range("H" & Rows.count).End(xlUp).Row
If x > 100 Then x = 100
Range("Z10") = Application.WorksheetFunction.CountIf(Range("H5:H" & x), "Y")
x = 0
ElseIf ws.Name = "TABF10" Then
x = ws.Range("F" & Rows.count).End(xlUp).Row
If x > 100 Then x = 100
Range("Z11") = Application.WorksheetFunction.CountIf(Range("F5:F" & x), "Y")
x = 0
ElseIf ws.Name = "TABF125" Then
x = ws.Range("G" & Rows.count).End(xlUp).Row
If x > 100 Then x = 100
Range("Z12") = Application.WorksheetFunction.CountIf(Range("G5:M" & x), "Y")
x = 0
ElseIf ws.Name = "TABF126" Then
x = ws.Range("G" & Rows.count).End(xlUp).Row
If x > 100 Then x = 100
Range("Z13") = Application.WorksheetFunction.CountIf(Range("G5:K" & x), "Y")
x = 0
ElseIf ws.Name = "TABF15" Then
x = ws.Range("F" & Rows.count).End(xlUp).Row
If x > 100 Then x = 100
Range("Z14") = Application.WorksheetFunction.CountIf(Range("F5:BC" & x), "Y")
x = 0
ElseIf ws.Name = "TABF2" Then
x = ws.Range("G" & Rows.count).End(xlUp).Row
If x > 100 Then x = 100
Range("Z16") = Application.WorksheetFunction.CountIf(Range("G5:BD" & x), "Y")
x = 0
ElseIf ws.Name = "TABF3" Then
x = ws.Range("G" & Rows.count).End(xlUp).Row
If x > 100 Then x = 100
Range("Z17") = Application.WorksheetFunction.CountIf(Range("G5:AE" & x), "Y")
x = 0
ElseIf ws.Name = "TABFR113" Then
x = ws.Range("G" & Rows.count).End(xlUp).Row
If x > 100 Then x = 100
Range("Z20") = Application.WorksheetFunction.CountIf(Range("G5:M" & x), "Y")
x = 0
ElseIf ws.Name = "TABFR73" Then
x = ws.Range("J" & Rows.count).End(xlUp).Row
If x > 100 Then x = 100
Range("Z26") = Application.WorksheetFunction.CountIf(Range("J5:J" & x), "Y")
x = 0
ElseIf ws.Name = "TABFR77" Then
x = ws.Range("E" & Rows.count).End(xlUp).Row
If x > 100 Then x = 100
Range("Z27") = Application.WorksheetFunction.CountIf(Range("E5:E" & x), "Y")
x = 0
ElseIf ws.Name = "TABFT281" Then
x = ws.Range("F" & Rows.count).End(xlUp).Row
If x > 100 Then x = 100
Range("Z29") = Application.WorksheetFunction.CountIf(Range("F5:L" & x), "Y")
x = 0
ElseIf ws.Name = "TABF282" Then
x = ws.Range("E" & Rows.count).End(xlUp).Row
If x > 100 Then x = 100
Range("Z30") = Application.WorksheetFunction.CountIf(Range("E5:L" & x), "Y")
x = 0
ElseIf ws.Name = "TABFT283" Then
x = ws.Range("H" & Rows.count).End(xlUp).Row
If x > 100 Then x = 100
Range("Z31") = Application.WorksheetFunction.CountIf(Range("H5:N" & x), "Y")
x = 0
ElseIf ws.Name = "TABF284" Then
x = ws.Range("I" & Rows.count).End(xlUp).Row
If x > 100 Then x = 100
Range("Z32") = Application.WorksheetFunction.CountIf(Range("I5:O" & x), "Y")
x = 0
ElseIf ws.Name = "TABFT6" Then
x = ws.Range("F" & Rows.count).End(xlUp).Row
If x > 100 Then x = 100
Range("Z34") = Application.WorksheetFunction.CountIf(Range("F5:F" & x), "Y")
x = 0
ElseIf ws.Name = "TABF9" Then
x = ws.Range("F" & Rows.count).End(xlUp).Row
If x > 100 Then x = 100
Range("Z38") = Application.WorksheetFunction.CountIf(Range("F5:F" & x), "Y")
x = 0
ElseIf ws.Name = "TABFR127" Then
x = ws.Range("G" & Rows.count).End(xlUp).Row
If x > 100 Then x = 100
Range("Z22") = Application.WorksheetFunction.CountIf(Range("G5:G" & x), "A")
x = 0
ElseIf ws.Name = "TABFT5" Then
x = ws.Range("G" & Rows.count).End(xlUp).Row
If x > 100 Then x = 100
Range("Z33") = Application.WorksheetFunction.CountIf(Range("G5:G" & x), "G")
x = 0
End If
Next ws
End Sub

 

[Dartagnan]

Nothing on all worksheets?

I tried your code on the first 4 worksheets and I get the correct number on each.

 

[tt.viquel]

I realised what the problem was i hadnt specified i wanted the results to be displayed pn the same sheet. and i managed to fix it, Thanks.

I als owanted to find out fi i want to do the similiar thing with a counta and sumproduct. how about would i do that e.g.

=SUMPRODUCT(KTPT81T!G:G)

and

=COUNTA(KTPTZIT!H:H)-2

again for both i then have 5 or more tables to add.