I have a spreadsheet that serves as our resource calendar with two worksheets. One ('Data Entry') has time entered daily. Every row in 'Data Entry' is a resource name (e.g.: John Smith) and every column is a day of a month (e.g.: Jan.22, Jan.23 etc.). In my second worksheet ('Weekly summary') I have the same layout, that is the rows are the resources in the same order as on the first worksheet, but instead of daily columns, I have weekly columns.

I'm trying to sum up all daily time entries per resource into a weekly summary. For example if John Smith has the following entries in the 'Data Entry' worksheet: 8 ours for Jan.21, 8 hours for Jan.22, 4 hours for Jan.23 and no entries for Jan.24-28, in the 'Weekly Summary' worksheet, I'd like to add all the time entries for columns Jan.21 through Jan.28 where the row has John Smith (should display 20 hours for the week of Jan.21)

Here's what I've used so far:

=SUMPRODUCT('Data Entry'!K$2:CN$80*('Data Entry'!C$2:C$80="John Smith"))

The formula works well but only a certain number of rows. If I extend the range from the current C2-C80 to C2-C82 is returns #VALUE! error.
BUT, if I move the entire range from C2-C80 to C5-C83 it works. Ir seems like it's having problem with the range size.

Any ideas?

Thanks,
Matthew