+ Reply to Thread
Results 1 to 12 of 12

SumProduct and Weekday problem

  1. #1
    Forum Contributor
    Join Date
    06-23-2014
    Location
    Dubbo, Australia
    MS-Off Ver
    EXCEL 2019
    Posts
    180

    SumProduct and Weekday problem

    Hi all,

    I have a spreadsheet with two tabs

    Tab A contains data (many fields across the row, hundreds of rows) based on daily values, and column C is the date column
    Tab B is a summary sheet, and I am trying to do a simple count of how many instances based on the day of the week, from all the data in Tab A, using Tab A's date column.

    Is the weekday element the wrong argument to use in this formula - and/or because Saturday is not normally classed as a weekday it could be forming the problem??

    =SUMPRODUCT(--(WEEKDAY('Tab1'!$C$2:$C$510,11)=$A71))

    $A71 is 6 ($B71 = Saturday, text field for end-user info purposes only)

    The cells above this summary cell (in Tab B, $A66:$A70 (1 thru 5), $B66:$B70 are "Monday" thru "Friday") are calculating fine, but this one cell $A71 blows out by about 200 times the weekly count, when it should be less!
    Just as an example of how much of a blow-out, the average for weekdays is 25, manually painful count average for Saturdays is 15, but $A71 is showing 380!

    thanks in anticipation
    Pete
    Struggling Solutions Architect

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    Re: SumProduct and Weekday problem

    Quote Originally Posted by dubcap01 View Post
    =SUMPRODUCT(--(WEEKDAY('Tab1'!$C$2:$C$510,11)=$A71))
    Your Return type is wrong.
    It can not be 11 so I assume it's omitted...

    1 or omitted Numbers 1 (Sunday) through 7 (Saturday).
    2 Numbers 1 (Monday) through 7 (Sunday).
    3 Numbers 0 (Monday) through 6 (Sunday).

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

  3. #3
    Forum Contributor
    Join Date
    06-23-2014
    Location
    Dubbo, Australia
    MS-Off Ver
    EXCEL 2019
    Posts
    180

    Re: SumProduct and Weekday problem

    Thanks zBor,

    I already tried the '2' option, in fact I have tried every possible option of these, but I still keep getting the 200* answer!
    weekdays, average 25, weekend Saturday 380!
    I am at a loss, perhaps I should get the user's spreadsheet and rebuild it properly!
    The one thing I cannot ever like is picking up someone else's badly designed spreadsheet and try and fix it, because one (me) n
    ever knows where the root cause of the problem lies!

    cheers
    Pete

  4. #4
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    1,454

    Re: SumProduct and Weekday problem

    Hi, Seems to be a problem with blank cells, add one more criteria, it will work

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Click just below left if it helps, Booşathì

  5. #5
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    Re: SumProduct and Weekday problem

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  6. #6
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: SumProduct and Weekday problem

    @boopathiraja

    Good spot!

    Now whatever made Microsoft think that a blank cell returning the equivalent of "Saturday" via this function was a good idea?!

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  7. #7
    Forum Contributor
    Join Date
    06-23-2014
    Location
    Dubbo, Australia
    MS-Off Ver
    EXCEL 2019
    Posts
    180

    Re: SumProduct and Weekday problem

    thanks guys, I have attached a sample spreadsheet, de-sensitized, but I think the problem may be the blanks as mentioned by Boopathirajah, but that solution doesn't work!
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    06-23-2014
    Location
    Dubbo, Australia
    MS-Off Ver
    EXCEL 2019
    Posts
    180

    Re: SumProduct and Weekday problem

    oops , forgot to mention, it's the 'other graphs' tab, and only one cell is misbehaving! this is why it's confounding!
    am aware of the circular reference warning, but I don't think it affects the data - and as I said, it's not my spreadsheet but somebody else's that I have been asked to fix!

  9. #9
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    Re: SumProduct and Weekday problem

    Looks like boopathiraja solution works to me:

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

  10. #10
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    1,454

    Re: SumProduct and Weekday problem

    Hi, ref the attachment with formula's updated..
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    06-23-2014
    Location
    Dubbo, Australia
    MS-Off Ver
    EXCEL 2019
    Posts
    180

    Re: SumProduct and Weekday problem

    [SOLVED] Thanks Boopathiraja, that fixed it!

  12. #12
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    1,454

    Re: SumProduct and Weekday problem

    Hi, thanks for the feedback

+ 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] WEEKDAY function problem
    By Mikebra in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-12-2013, 03:35 AM
  2. Conditional formatting problem. If a WEEKDAY is a 7 OR a 1
    By thewrathful in forum Excel General
    Replies: 4
    Last Post: 04-18-2010, 11:26 AM
  3. Problem with the WEEKDAY function
    By petevang in forum Excel General
    Replies: 1
    Last Post: 08-28-2006, 06:28 PM
  4. Difficult formula SUMPRODUCT,MATCH,WEEKDAY
    By edwardpestian in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-08-2006, 06:53 AM
  5. Weekday Problem
    By deanholmes in forum Excel General
    Replies: 4
    Last Post: 04-15-2006, 07:50 AM

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