Excel – COUNTIF function if day of week is Tuesday


I need to count a column only if the corresponding cell in another column is a Tuesday. An example image is:

SO25561294 question example

Initially, I was trying to use a COUNTIFS function paired with a WEEKDAY but I can't get it to work.




Each unit needs to be counted on Tuesday every week. If they count a day before or after it's considered late. What needs to happen, is each unit needs to have a count of the number of days that they did count and then the number of days that they didn't count. In the past, I have accomplished this last part by a simple arithmetic formula based on the number of days in the month. In addition to the two counts, I also need any missed cells to be filled in with a red background.

The actual sheet has several tables in the same format ranging from 1 column to 65 columns.

Best Solution

Please try, in B34:


entered with Ctrl+Shift+Enter and copied across to D34.

I am assuming you are able to count the number of Tuesdays in the relevant month and complete Row35 by deducting from that number the value in the cell immediately above.


You have also what is really a completely separate question in your post (the red background) which I think is best handled with conditional formatting. Select B:D and in Conditional Formatting, New Rule... select Use a formula to determine which cells to format and under Format values where this formula is true: enter:


Click Format..., select Fill and red, OK, OK.

Related Question