Why would a Range including over 524 rows not work?

**jebediah** · 09-05-2007, 01:30 PM

What would make this not work:

=SUM((Time1!$L$2:$L$529=$A77)*(Time1!$H$2:$H$529=D$1)*Time1!$J$2:$J$529)

When this does:

=SUM((Time1!$L$2:$L$524=$A77)*(Time1!$H$2:$H$524=D$1)*Time1!$J$2:$J$524)

All I've done was extend the number of rows being checked for SUM (from 524 to 529), but somehow it returns 0 where the original returns the expected sum. Those rows after 524 aren't any different than those before.

I'm baffled. Any help would be great.

Thanks!

**Special-K** · 09-05-2007, 01:35 PM

Change the range to look at 524 to 529
If that returns zero then its not "more than 524 rows" thats at fault its a value between rows 524-529

**jebediah** · 09-05-2007, 02:02 PM

I didn't expect to use this for so long, but here I am.

So, those functions worked with hundreds of blank rows up to 524. I expected it to keep working when I increased that value. After all, it's just more blank rows like before. But now it's not happening.

Honestly, that's the only change, nothing else is different, nothing has changed from functionality to non-functionality other than referencing any # of rows over 524.

**FrankBoston** · 09-05-2007, 02:12 PM

You are reporting formulas that look ok. In this type of situation, you may not be looking at the actual formulas in your worksheet. Look hard without assuming anything.

These are array formulas. Are you entering them with CTL-SHIFT-ENTER, as you should?

**jebediah** · 09-05-2007, 02:17 PM

I selected all the blank cells and cleared content. Then it worked fine. Maybe there was a cell with a formula or an entry that I couldn't see easily (a period?).

Thanks for the help folks.

**daddylonglegs** · 09-05-2007, 02:24 PM

even a space anywhere in J2:J529 would cause the formula to fail, you could try using a SUMPRODUCT formula where that wouldn't have such a catastrophic effect - and also doesn't need to be "array-entered"

=SUMPRODUCT(--(time1!$L$2:$L$529=$A77),--(time1!$H$2:$H$529=D$1),time1!$J$2:$J$529)