Weekday counting from list of dates, ignoring non date cells

[Badvgood]

Hi,

=SUMPRODUCT((WEEKDAY($B$4:$B$32,2)=E4)*($B$4:$B$32<>""))

I was hoping that by adding this to the end of my formula, it would ignore cells that are in the range that are not a date(IE Not a number), but it does not.

When I increase the range to cover B34, I get #value

How can I modify my formula to overcome this?

Screenshot 2020-03-27 at 11.50.19.png

Thanks Leon

[Bo_Ry]

Did you increase both ranges?

=SUMPRODUCT((WEEKDAY($B$4:$B$99,2)=E4)*($B$4:$B$99>0))

[Badvgood]

Did you increase both ranges?

=SUMPRODUCT((WEEKDAY($B$4:$B$99,2)=E4)*($B$4:$B$99>0))

Yes, increased both ranges. Unfortunately, your solution also gives the same error.

Attachment 669309

[Bo_Ry]

Your attachment show "Invalid Attachment specified".

I guess B33 is null string

Please try
=SUMPRODUCT((WEEKDAY(N(+$B$4:$B$99),2)=E4)*($B$4:$B$99<>""))

[Badvgood]

Your attachment show "Invalid Attachment specified".

I guess B33 is null string

Please try
=SUMPRODUCT((WEEKDAY(N(+$B$4:$B$99),2)=E4)*($B$4:$B$99<>""))

Perfect, that has got it, thank you.

What does the N in the formula do exactly?

Thanks Leon

[Bo_Ry]

N change text to 0, Weekday can calculate 0 but can't calculate text.