And OR in WorksheetFunction.CountIf and SumIfs

[jameslio]

Hi there

I have an excel sheet that I need to count value <5 and >0

Sheets("READ_ME").Cells(40, 2) = WorksheetFunction.CountIf(Sheets("JOIN_DATA").Range("i:i"), "<5"

and to sum if <5 and >0

Sheets("READ_ME").Cells(41, 2) = WorksheetFunction.SumIfs(Sheets("JOIN_DATA").Range("g:g"), Sheets("JOIN_DATA").Range("i:i"), "<5")

How do I use AND function in this?

Thanks

 

[Takae]

Same as worksheetfunction.

Sheets("READ_ME").Cells(40, 2) = WorksheetFunction.CountIf(Sheets("JOIN_DATA").Range("i:i"), "<5",Sheets("JOIN_DATA").Range("i:i"),">0")

Sheets("READ_ME").Cells(41, 2) = WorksheetFunction.SumIfs(Sheets("JOIN_DATA").Range("g:g"), Sheets("JOIN_DATA").Range("i:i"), "<5",Sheets("JOIN_DATA").Range("i:i"), ">0")

Tidy

With Sheets("JOIN_DATA")
    Sheets("READ_ME").cells(40, 2) = WorksheetFunction.CountIf(.Range("i:i"), "<5", .Range("i:i"), ">0")
    Sheets("READ_ME").cells(41, 2) = WorksheetFunction.SumIfs(.Range("g:g"), .Range("i:i"), "<5", .Range("i:i"), ">0")
End With

 

[jameslio]

Thanks but your code does not work it said Compile Error Wrong number of arguments or invalid property assignment

Same as worksheetfunction.

Sheets("READ_ME").Cells(40, 2) = WorksheetFunction.CountIf(Sheets("JOIN_DATA").Range("i:i"), "<5",Sheets("JOIN_DATA").Range("i:i"),">0")

Sheets("READ_ME").Cells(41, 2) = WorksheetFunction.SumIfs(Sheets("JOIN_DATA").Range("g:g"), Sheets("JOIN_DATA").Range("i:i"), "<5",Sheets("JOIN_DATA").Range("i:i"), ">0")

Tidy

With Sheets("JOIN_DATA")
    Sheets("READ_ME").cells(40, 2) = WorksheetFunction.CountIf(.Range("i:i"), "<5", .Range("i:i"), ">0")
    Sheets("READ_ME").cells(41, 2) = WorksheetFunction.SumIfs(.Range("g:g"), .Range("i:i"), "<5", .Range("i:i"), ">0")
End With

 

[jameslio]

Hi all thanks nevermind got it to work I have to put "s" at the back of CountIfs. Jesus can VBA be that bad in programming? Prefer C