How can I make this work - sorry for the vague title!

[Fazila]

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Cohort!$L$13:$L$2002,ROW(Cohort!$L$13:$L$2002)-MIN(ROW(Cohort!$L$13:$L$2002)),,1))*(Cohort!$L$13:$L$2002>5<21))

It works for =SUMPRODUCT(SUBTOTAL(3,OFFSET(Cohort!$L$13:$L$2002,ROW(Cohort!$L$13:$L$2002)-MIN(ROW(Cohort!$L$13:$L$2002)),,1))*(Cohort!$L$13:$L$2002<=5))

However, for the next row I need values that are greater than 5 but less than equal to 20.

Thanks

Fazila

 

[Domenic]

Try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Cohort!$L$13:$L$2002,ROW(Cohort!$L$13:$L$2002)-MIN(ROW(Cohort!$L$13:$L$2002)),,1))*(Cohort!$L$13:$L$2002>5)*(Cohort!$L$13:$L$2002<21))

By the way, a suitable title might have been "Count based on multiple criteria and filtered list".

Hope this helps!

 

[Fazila]

Thanks Domenic I managed to make the formula work using

=SUMPRODUCT(--(Cohort!$L$13:$L$2002>5),--(Cohort!$L$13:$L$2002<=20),SUBTOTAL(3,OFFSET(Cohort!$L$13:$L$2002,ROW(Cohort!$L$13:$L$2002)-MIN(ROW(Cohort!$L$13:$L$2002)),,1)))

Thanks for posting

 

[Domenic]

Yep, that would do it too...

Cheers!