How to solve Excel count of saturday-CodePudding

I have a list of dates in MS Excel in column A. I am using Excel 2016.

I want to find out how many dates are Sunday and separately Saturday. I dont know exactly how many rows in sheet so I need to use the whole column. I am using the following formula:

Count of Saturdays:

=SUMPRODUCT(--(WEEKDAY(A:A)=7))

Count of Sundays:

=SUMPRODUCT(--(WEEKDAY(A:A)=1))

The same formula is used for Friday only for testing. Count of Fridays:

=SUMPRODUCT(--(WEEKDAY(A:A)=6))

The Friday and Sunday works well as I see, but Saturday returns an icorrect value (it seems to me works on blank values)

Could be is that a WEEKDAY function bug? Somebody has the same problem? Any Idea to solve the problem?

CodePudding user response：

Add an additional clause to exclude blanks. And don't use entire column references within SUMPRODUCT. Either reformat your column entries as a Table, after which your formula would be:

=SUMPRODUCT(--(WEEKDAY(Table1[Date])=7),--(Table1[Date]<>""))

else use a construction which determines the last-used cell within that column:

=SUMPRODUCT(--(WEEKDAY(A1:INDEX(A:A,LastRow))=7),--(A1:INDEX(A:A,LastRow)<>""))

where LastRow is defined within Name Manager as:

=MATCH(9^9,Sheet1!$A:$A)

(Obviously amend Sheet1 in this definition as required.)

CodePudding user response：

Considering

Sunday=1 Saturday=6

=SUMPRODUCT(N(WEEKDAY(A:A)={1,6}))