+ Reply to Thread
Results 1 to 6 of 6

Sumproduct error with weekday and blank cells

  1. #1
    Registered User
    Join Date
    01-31-2012
    Location
    Saigon, Vietnam
    MS-Off Ver
    Excel 2010
    Posts
    3

    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!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    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: copy to clipboard
    Please Login or Register  to view this content.


    and for the weekends:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    S7 and T7 down respectively.

    Hope this helps.
    DBY

  3. #3
    Registered User
    Join Date
    01-31-2012
    Location
    Saigon, Vietnam
    MS-Off Ver
    Excel 2010
    Posts
    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. #4
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Sumproduct error with weekday and blank cells

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

    Formula: copy to clipboard
    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. #5
    Registered User
    Join Date
    01-31-2012
    Location
    Saigon, Vietnam
    MS-Off Ver
    Excel 2010
    Posts
    3

    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)
    Last edited by ledpham; 08-19-2015 at 01:22 AM.

  6. #6
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] SumProduct and Weekday problem
    By dubcap01 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-04-2014, 11:44 PM
  2. Replies: 7
    Last Post: 08-31-2013, 09:18 PM
  3. Replies: 15
    Last Post: 04-25-2012, 05:21 PM
  4. Sumproduct using blank cells
    By Wskip49 in forum Excel General
    Replies: 4
    Last Post: 12-18-2008, 03:52 PM
  5. Difficult formula SUMPRODUCT,MATCH,WEEKDAY
    By edwardpestian in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-08-2006, 06:53 AM
  6. sumproduct with 0/blank cells
    By Matt in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-15-2005, 06:30 PM
  7. [SOLVED] SUMPRODUCT - Blank Cells
    By Peggy Sue in forum Excel General
    Replies: 3
    Last Post: 11-02-2005, 06:17 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1