Formula needs to refer to a dynamic range on another sheet

[zookeepertx]

Hello all!

I have a workbook with 3 sheets in it. The first sheet summarizes certain data from the other 2 sheets. Other people regularly add and subtract rows on the data sheets and will sometimes insert blank rows; I don't know why, though.

I need the formulas on the summary sheet to refer to ALL of the data in - for example - column F on sheet 2. How can I get the formula to look at all the rows with data, not just down to the first blank row? If I just use a reference to the entire column - ($F:$F) - the formula runs really slowly. When I evaluate the formula, when it shows the result of ($F:$F), it shows (8,8,4,8,8,4,4,8,4,4,6,4,1,1,1,1,1,1,1.......) and the 1s go on FOREVER! (I assume for however many rows are available on a spreadsheet) Then, another part of the formula references another column and it happens again!

I'm a little afraid to make the data into named ranges because I know the people using the workbook could find SOME way to throw a wrench into it. I'd make a macro for it, but it'd just be so nice to use formulas on the summary and have it always be up-to-date!

Here's an example of the formula in C5 on the summary sheet:

=SUMPRODUCT(--(IFERROR(MONTH(COMPLETE2!$A:$A),0)=MONTH(DATEVALUE(C$3 & "1")))*(COMPLETE2!$F:$F="ALLOCATION"))

It gets the month in C3 of the summary sheet, finds all the rows on the data sheet (called "COMPLETE2") where A has the same month. Then looks at column F on the data sheet and counts how many rows for the given month say "ALLOCATION" in F. How can I get the formula to find the LAST occupied row of data, not stop at the first blank row?

Thanks!

Jenny