I need to count a column only if the corresponding cell in another column is a Tuesday. An example image is:
只有当另一列中的相应单元格是星期二时，我才需要数一列。图像是一个例子:
Initially, I was trying to use a COUNTIFS function paired with a WEEKDAY but I can't get it to work.
最初，我尝试使用一个与工作日配对的COUNTIFS函数，但我无法让它正常工作。
=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.
实际的表有几个相同格式的表，从一列到65列。
2 个解决方案
#1
7
Please try, in B34:
请尝试,B34:
=SUM((WEEKDAY($A2:$A32)=3)*(B2:B32<>""))
entered with Ctrl+Shift+Enter and copied across to D34.
输入并按Ctrl+Shift+Enter，复制到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.
我假设您能够通过从该数字中减去上面单元格中的值来计算相关月份的星期二数并完成第35行。
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:
你的文章(红色背景)中有一个完全独立的问题，我认为最好用条件格式来处理。选择B:D，在条件格式，新规则…选择使用公式确定要格式化哪些单元格，并在此公式为真的格式值下输入:
=AND(WEEKDAY($A1)=3,B1="")
Click Format..., select Fill and red, OK, OK.
点击格式…，选择Fill和red, OK, OK。
#2
0
You can insert an column B and fill it with the following formula:
你可以插入一个列B，并填入下面的公式:
=IF(WEEKDAY(A2)=3,1,"")
=IF(WEEKDAY(A3)=3,1,"")
...
Then you can count them with the following formulas:
然后你可以用以下公式来计算:
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)
You can hide the column B if you wish.
如果愿意，可以隐藏B列。
#1
7
Please try, in B34:
请尝试,B34:
=SUM((WEEKDAY($A2:$A32)=3)*(B2:B32<>""))
entered with Ctrl+Shift+Enter and copied across to D34.
输入并按Ctrl+Shift+Enter，复制到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.
我假设您能够通过从该数字中减去上面单元格中的值来计算相关月份的星期二数并完成第35行。
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:
你的文章(红色背景)中有一个完全独立的问题，我认为最好用条件格式来处理。选择B:D，在条件格式，新规则…选择使用公式确定要格式化哪些单元格，并在此公式为真的格式值下输入:
=AND(WEEKDAY($A1)=3,B1="")
Click Format..., select Fill and red, OK, OK.
点击格式…，选择Fill和red, OK, OK。
#2
0
You can insert an column B and fill it with the following formula:
你可以插入一个列B，并填入下面的公式:
=IF(WEEKDAY(A2)=3,1,"")
=IF(WEEKDAY(A3)=3,1,"")
...
Then you can count them with the following formulas:
然后你可以用以下公式来计算:
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)
You can hide the column B if you wish.
如果愿意，可以隐藏B列。