5

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

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 ?

8

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
  • 2
    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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.