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

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

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.

@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

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!

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!

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

[SOLVED] Thanks Boopathiraja, that fixed it!

Hi, thanks for the feedback