Re Countif

[Martin Wheeler]

xl2003
I am trying to count the number of instances in range L7:Q12 in which the L
cell is < 0 and the Q cell <= 4, as in

A = Application.WorksheetFunction.CountIf(.Range("L7:L12"), "< 0")
B = Application.WorksheetFunction.CountIf(.Range("Q7:Q12"), "< = 4")

I am trying to 'combine' the above 2 functions like you can in excel, as in

A = Application.WorksheetFunction.CountIf(CountIf(.Range("L7:L12"), "<
0"),CountIf(.Range("Q7:Q12"), "<= 4"))

but is does not like the first CountIf.

Any help would be greatly appreciated

Ta,
Martin

 

[Tom Ogilvy]

I am trying to 'combine' the above 2 functions like you can in excel

I think you are remembering something that isn't true.

=Sumproduct(--(L7:L12<0),--(Q7:Q12<=4))


in code

A = Evaluate("Sumproduct(--(L7:L12<0),--(Q7:Q12<=4))")

 

[Martin Wheeler]

Hi Tom,
Thanks for your help.
It works great.
Ta,
Martin