Dynamic range... stuff...

I feel like I am THIS CLOSE to having this figured out but I am missing something somewhere.

I have two sheets. First is named overview and the second is named DATA

I am using this statement on the overview sheet to grab data from the DATA sheet:

=COUNTIFS(DATA!L2:DATA!L369,"Sat",DATA!M2:DATA!M369,1)+COUNTIFS(DATA!L2:DATA!L369,"Sun",DATA!M2:DATA!M369,1)

What I need to do is dynamically set the DATA!L369 number. I am pasting data into the DATA sheet and this DATA!L369 number could be anything (the 369 could be any number but the DATA!L part always remains the same). So it needs to say, "Look in the range L2 up to whatever the last cell in column L that has data in it is"

I have tried using a combination of COUNTA with Index but simply cannot get it right.

Please help me Obi Wan.

Lots of great info here: http://www.ozgrid.com/Excel/DynamicRanges.htm

Dynamic Ranges are good stuff. And you're on the right path already.

Post deleted

Originally Posted by FDibbins

Hi and welcome to the forum

Apart from asking for a more descriptive thread title (as per rule 1), I would ask why you need to have the length set in the 1st place?

Why not just have...
=COUNTIFS(DATA!$L:DATA!$L,"Sat",DATA!$M:DATA!$M,1)+COUNTIFS(DATA!$L:DATA!$L,"Sun",DATA!$M:DATA!$M,1)

To be honest - I couldn't figure out how to title this one and finally just said ... stuff. It said something like, "Dynamic range in a formula when referencing another sheet with the second number after the colon changing." Anyway, I'll try harder next time.

Thank you for the idea here. I think I figured that referencing the entire column would error out if no data was there, but I now see that makes no sense! I'll give these a twirl. Thanks a lot!

Thank you very much!

Originally Posted by Tony Valko

The COUNTIFS function is an efficient function. Just reference the entire columns.

=SUM(COUNTIFS(DATA!L:L,{"Sat","Sun"},DATA!M:M,1))

If you have data down to row 375 these formulas are equally efficient:

=SUM(COUNTIFS(DATA!L:L,{"Sat","Sun"},DATA!M:M,1))
=SUM(COUNTIFS(DATA!L2:L375,{"Sat","Sun"},DATA!M2:M375,1))