"#Value" error with Sumproduct

[Windyisle]

Hey there,

I"m so confused. I have a sumproduct formula that works seemingly only if I keep the number of cells it's searching below a certain range.

This works fine:
=SUMPRODUCT(--(WEEKDAY('Daily Rundown'!$A$5:$A$83,2)=1),--(ISNUMBER(SEARCH("*advocate*",'Daily Rundown'!$F$5:$F$83))),--(ISNUMBER(SEARCH("00:00:15:00",'Daily Rundown'!$D$5:$D$83))))

This returns the '#Value' error:
=SUMPRODUCT(--(WEEKDAY('Daily Rundown'!$A$5:$A$400,2)=1),--(ISNUMBER(SEARCH("*advocate*",'Daily Rundown'!$F$5:$F$400))),--(ISNUMBER(SEARCH("00:00:15:00",'Daily Rundown'!$D$5:$D$400))))

If more information on my worksheet is needed: I'm extracting data to tables, one for each day of the week, from an output list of the whole week. I can go through and guess at where Monday might stop and start and do the same for Tuesday...etc. (which is why it goes just from A5 to A83 in the first example) But I have all kinds of different data to extract and that would mean tediously changing the numbers in the formula on each daily table, rather than just quickly changing the weekday number.

Any help would be much appreciated.

 

[RoryA]

Do you have any errors or text values in A84:A400 on the Daily rundown sheet?

 

[Windyisle]

That's it!

The last entry in that column is 'page 1'. I'm not sure what to do about this. I'll be pasting in a new week every week and each one will be a different length. Is there a way I can get it to ignore the very last cell if the database is of variable length? Sort of like a 'select all then -1' from the range to look in?

Thanks for the help.

 

[RoryA]

You can use a formula like:
=MATCH(9E+300,A:A,1)
to find the last row in A containing a number value (dates are numbers), and then use that row number in your formulas.

 

[Windyisle]

Awesome, thanks!