SumProduct and Weekday problem

1. 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

2. Re: SumProduct and Weekday problem

Originally Posted by dubcap01
=SUMPRODUCT(--(WEEKDAY('Tab1'!\$C\$2:\$C\$510,11)=\$A71))
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:
`Please Login or Register  to view this content.`

3. 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. Re: SumProduct and Weekday problem

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

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

5. 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. 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

7. 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!

8. 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. Re: SumProduct and Weekday problem

Looks like boopathiraja solution works to me:

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

10. Re: SumProduct and Weekday problem

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

11. Re: SumProduct and Weekday problem

[SOLVED] Thanks Boopathiraja, that fixed it!

12. Re: SumProduct and Weekday problem

Hi, thanks for the feedback

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

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