counting with two conditions!

[ALex60]

Hi All,
I want to count with two conditions in two different columns.

WAIT AGE_YR
0 82
0 70
0 82
0 73
0 68
I used the following to count number of "wait" between 0 and 5 that has "AGE" between 70 and 85, but I get 1 which is wrong I should get 4.

=SUMPRODUCT(IF((COUNTIF(A2:A6, "<=5") - COUNTIF(A2:A6, "<0" )),1,0), IF((COUNTIF(B2:B6, "<=85") - COUNTIF(B2:B6, "<70" )),1,0))

can anyone help me?
Thanks

[NBVC]

Try:

=sumproduct((a2:a6<=5)*(a2:a6>=0)*(b2:b6<=85)*(b2:b6>=70))

[ALex60]

Thanks a lot

It worked!
it was very efficient

[ALex60]

Is there anyway I can do the same thing without indicating the range of the columns? because in the dataset that i am working on, the columns have different size...

[NBVC]

If you are in Excel 2007, you can you whole columns

=sumproduct((a:a<=5)*(a:a>=0)*(b:b<=85)*(b:b>=70))

If you are in earlier versions, you can indicate almost the whole column

=sumproduct((a2:a65536<=5)*(a2:a65536>=0)*(b2:b65536<=85)*(b2:b65536>=70))

[ALex60]

Thanks again....