# Sumproduct error with weekday and blank cells

1. ## Sumproduct error with weekday and blank cells

I tried to setup a time sheet which separates weekday and weekend hours with SUMPRODUCT. There are 16 columns for the longest month (December), and it works ok in this condition. When I entered "2" in the month cell for February, I got #VALUE! error due to the blank cells. Thanks in advance for your help!

2. ## Re: Sumproduct error with weekday and blank cells

Hello
Instead of using the WEEKDAY function which is returning #VALUE errors, try the following instead. For the regular weekdays:

Formula:
`Please Login or Register  to view this content.`

and for the weekends:

Formula:
`Please Login or Register  to view this content.`

S7 and T7 down respectively.

Hope this helps.
DBY

3. ## Re: Sumproduct error with weekday and blank cells

Thanks so much for your help. The weekdays work beautifully, but the weekends don't. They all return 0. My guess is that the criteria of "=1" and "=7" have to be OR instead of AND. But even after taken out one of the conditions, the formula still returns 0.

4. ## Re: Sumproduct error with weekday and blank cells

Just working off DBY's solution, you can use this formula for the weekends.

Formula:
`Please Login or Register  to view this content.`

Note: This works because the two conditions are mutually exclusive, and summing them only results in 1s and 0s.

5. ## Re: Sumproduct error with weekday and blank cells

Thank you, I just found that this one worked as well

=SUMPRODUCT(--(\$C\$6:\$R\$6=1)--(\$C\$6:\$R\$6=7),\$C7:\$R7)

6. ## Re: Sumproduct error with weekday and blank cells

Yup, that works the same way (-- or minus minus is +). My note from above still holds, that is, you may run into the risk of results being more than 1. However, in this case, it should never happen.

If that resolves your query, can you kindly mark this thread as Solved by clicking on Thread Tools above your original post and subsequently click on Mark This Thread as Solved.

There are currently 1 users browsing this thread. (0 members and 1 guests)