# 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 ?

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.

• Thank you! So as I understand at the very end of formula =2 means count Mon, =3 Tue? – Powisss May 2 '17 at 17:43
• @Powisss yes that is correct. – Scott Craner May 2 '17 at 17:44
• Additionally, if you wanted to have one formula to count all the weekdays, you could change it to `=SUMPRODUCT(--(WEEKDAY(U2:INDEX(U:U,MATCH(1E+99,U:U)),2)<6))`. Adding a `2` as the `return_value` in the `Weekday` function shifts the starting point so 1=Monday and 7=Sunday. Anything <6, therefore, is Mon-Fri. – Engineer Toast May 2 '17 at 20:22