Excel逻辑公式需求：为高亮Result列编写按规则返回0/1的公式

Let's build the formula step by step based on the rules you've shared so far. First, I'll assume your sheet has these key columns (adjust the column references if yours are different):

• Column A: Event ID (to group all legs belonging to the same event)
• Column C: Control Exist? (values are "Yes" or "No")
• Column D: Result (the highlighted column where we'll place the formula)

The Formula #

Paste this into cell D2, then drag it down to apply to all rows:

=IF(OR(AND(COUNTIFS($A:$A,$A2,$C:$C,"Yes")>0,COUNTIFS($A:$A,$A2,$C:$C,"No")>0), COUNTIFS($A:$A,$A2,$C:$C,"Yes")=COUNTIF($A:$A,$A2)), 0, 1)

How It Works #

Let's break down the logic to match your rules:

• Rule 1 Check: AND(COUNTIFS($A:$A,$A2,$C:$C,"Yes")>0,COUNTIFS($A:$A,$A2,$C:$C,"No")>0)
  This checks if the current event has both "Yes" and "No" entries in Control Exist?. If true, returns 0.
• Rule 2 Check: COUNTIFS($A:$A,$A2,$C:$C,"Yes")=COUNTIF($A:$A,$A2)
  This checks if all legs in the current event have "Yes" in Control Exist? (the number of Yes entries equals the total number of legs for the event). If true, returns 0.
• Remaining Case: If neither of the above is true, that means all legs in the event have "No" in Control Exist? — so we return 1.

If you end up getting the rest of the rules, we can easily tweak this formula to include additional conditions. Just share them when you have them!

内容的提问来源于stack exchange，提问作者Seth