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

Fazila

Board Regular
Joined
Nov 19, 2014
Messages
163
=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
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,309
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!
 
Last edited:

Fazila

Board Regular
Joined
Nov 19, 2014
Messages
163
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,466
Messages
5,528,972
Members
409,848
Latest member
Blomsten
Top