Counting weekdays

[Sven62]

Please tell me why this =SUMPRODUCT(WEEKDAY($B2:$B2048,1)=1)) works fine for Sunday and =2, =3 etc. work for Tuesday thru Friday. There are 148 rows with data to count and they all get counted correctly. The remaining rows are blank.

Except that...

=SUMPRODUCT(WEEKDAY($B2:$B2048,1)=7)) on Saturday returns "1912". It is apparently counting blank cells for some reason. Why does this happen only for Saturday?

I am using =SUMPRODUCT(--($B2:$B2048<>""),--(WEEKDAY($B2:$B2048,1)=7)) to correct for it. But why do I need to do that?

 

[Vidar]

Not so sure if either of your first two formula is working. The formulas are giving TRUE's and FALSE's.
You need to use "--" to turn them into ONE's and ZERO's as you are doing in your last formula.
So doesn't this one work for you?
=SUMPRODUCT(--(WEEKDAY($B2:$B2048,1)=7))

 

[Sven62]

OOPS you're right... they do have -- and they work but Saturday does not. Saturday returns 1912. It is counting blank cells.

 

[Vidar]

You're right, I wasn't aware of that. Tried WEEKDAY function on blank cells and it returns 7.
I might be wrong here but the day zero in Excel, 01.01.1900, was probably a saturday. The weekday function
probably see empty cells as zero and coercing the blank cells into a number and then interpreting it as a date

 

[Aladin Akyurek]

OOPS you're right... they do have -- and they work but Saturday does not. Saturday returns 1912. It is counting blank cells.

=SUMPRODUCT(--(WEEKDAY($B2:$B2048,1)=7),--ISNUMBER(B2:$B2048))

would alleviate the problem.

 

[barry houdini]

This formula will also ignore blanks

=SUMPRODUCT(--(TEXT($B2:$B2048,"ddd;;")="Sat"))

 

[Sven62]

That does work. Thank you!

 

[Sven62]

Thank you.

 

[Sven62]

You're right, I wasn't aware of that. Tried WEEKDAY function on blank cells and it returns 7.
I might be wrong here but the day zero in Excel, 01.01.1900, was probably a saturday. The weekday function
probably see empty cells as zero and coercing the blank cells into a number and then interpreting it as a date

I bet you are right. Freakin' genius.