Re Countif
 

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:L 12"), "< 0")
B = Application.WorksheetFunction.CountIf(.Range("Q7:Q 12"), "< = 4")

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

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

but is does not like the first CountIf.

Any help would be greatly appreciated

Ta,
Martin

Re Countif
 

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))")

--
Regards,
Tom Ogilvy


"Martin Wheeler" wrote in message
...
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:L 12"), "< 0")
B = Application.WorksheetFunction.CountIf(.Range("Q7:Q 12"), "< = 4")

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

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

but is does not like the first CountIf.

Any help would be greatly appreciated

Ta,
Martin

Re Countif
 

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


"Tom Ogilvy" wrote in message
...
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))")

--
Regards,
Tom Ogilvy


"Martin Wheeler" wrote in message
...
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:L 12"), "< 0")
B = Application.WorksheetFunction.CountIf(.Range("Q7:Q 12"), "< = 4")

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

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

but is does not like the first CountIf.

Any help would be greatly appreciated

Ta,
Martin