Excel – COUNTIF function if day of week is Tuesday

array-formulasconditional-formattingcountifexcelexcel-formula

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.

=COUNTIFS(B2:B32,TRUE,A2:A32,WEEKDAY(3))

or

=COUNTIFS(B2:B32,IF(A2=WEEKDAY(3),1,0))

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:

=SUM((WEEKDAY($A2:$A32)=3)*(B2:B32<>""))

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.

Afterthought:

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:

=AND(WEEKDAY($A1)=3,B1="")  

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

Related Question