Excel: Count cells that contain weekday

I have column with dates in column U. I changed them to "ddd" format, so now they are Mon, Tue etc.

I am trying to count how many times Mon is in cells between cell U2 and U1048576. I tried =COUNTIF(U2:U1048576,Mon) and =COUNTIF(U2:U1048576,"Mon"). Result is always 0. I tried same thing but searching for a number and it works. How do I do same thing for weekdays ?

Answer

Changing the format does not change the value. Your dates are still numbers to Excel, just formatted to show a string. As such Excel does not find a string.

COUNTIFS() will not work without a helper column. You need to use SUMPRODUCT():

=SUMPRODUCT(--(WEEKDAY(U2:INDEX(U:U,MATCH(1E+99,U:U)))=2))

SUMPRODUCT is an array type formula. Therefore one should not use full column references.

The U2:INDEX(U:U,MATCH(1E+99,U:U)) sets the reference range to only those with numbers. This way it is not doing unnecessary calculations.

WEEKDAY() returns the day number 1 = Sunday, 2 = Monday, ...

The SUMPRODUCT() will count when the criteria = TRUE. The -- Changes TRUE to 1 and FALSE to 0.