Geek Answers Handbook

COUNTIF function if the day of the week is Tuesday

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

SO25561294 question example

At first I tried to use the COUNTIFS function in tandem with WEEKDAY, but I can not get it to work.

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

or

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

Each unit must be counted on Tuesday every week. If they consider the day before or after it is considered late. What should happen, each block must have a count of the number of days that they counted, and then the number of days that they did not count. In the past, I have completed this last part with a simple arithmetic formula based on the number of days in a month. In addition to the two counts, I also need missing cells filled with a red background.

, 1 65 .

+4
2

, B34:

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

Ctrl + Shift + Enter D34.

, Row35, .

:

( ), , , . B: D , New Rule... select , , , "", : ​​:

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

""..., "" "", "", "".

+6

B :

=IF(WEEKDAY(A2)=3,1,"")
=IF(WEEKDAY(A3)=3,1,"")
...

:

Days counted       =COUNTIFS(C$2:C$32,">0",$B$2:$B$32,1)
Days not counted   =COUNTIFS(C$2:C$32,"",$B$2:$B$32,1)

B, .

0

More articles:


All Articles