Why do I get countif blanks ?

[rmccain]

Hello,

I am trying to make the following code work correctly but all I get is get a blank. Truthfully, I would like to make an array out of this but I am stumped.

Kindest regards to all those that help. Sample file is attached.

Sub ITest()

Dim ws As Worksheet
Dim Val As Variant 'Double
Dim LD As Long  '*


Application.ScreenUpdating = False

For Each ws In ActiveWorkbook.Worksheets
   With ws
 LD = .Range("A" & Rows.Count).End(xlUp).Row
    
Val = Application.WorksheetFunction.CountIfs(.Range("I:I"), "=100", Range("I:I"), "=200", Range("I:I"), "=300", Range("I:I"), "=400")
    
    Range("K2:K" & LD).FillDown
    'Range("K10").Value = Val
    
     End With
Next ws

Application.ScreenUpdating = True

End Sub

Aloha

[protonLeah]

COUNTIFS:
Each cell in a range is counted only if all of the corresponding criteria specified are true for that cell.

You have four criteria which must be simultaneously true which is impossible for the data/criteria.

[Trebor76]

Hi rmccain,

Try this:

Option Explicit
Sub Macro1()

    Dim ws  As Worksheet
    Dim Val As Double
    Dim LD  As Long
    
    Application.ScreenUpdating = False
    
        For Each ws In ActiveWorkbook.Worksheets
            With ws
                LD = .Range("A" & Rows.Count).End(xlUp).Row
                Val = Evaluate("COUNTIFS('" & ws.Name & "'!I:I,"">=100"",'" & ws.Name & "'!I:I,""<=400"")")
                .Range("K2:K" & LD).Value = Val
            End With
        Next ws
        
    Application.ScreenUpdating = True

End Sub

Regards,

Robert