如何在日期范围内使用COUNTIF仅统计一次重复项及条件格式咨询

Hey there, let's tackle your Excel questions one by one—they're great, practical use cases!

First off, plain COUNTIF can't handle deduplication on its own, but we can pair it with other functions to get the result you need. The approach depends on your Excel version:

For Excel 365/2021 (with dynamic array support) #

Use UNIQUE + FILTER + COUNTA for a clean, readable formula. Let's assume:

• Column A = your date range (e.g., A2:A100)
• Column B = the values you want to count uniquely (e.g., supplier IDs)
• Target date range: 01/01/2024 to 31/01/2024

The formula would be:

=COUNTA(UNIQUE(FILTER(B2:B100,(A2:A100>=DATE(2024,1,1))*(A2:A100<=DATE(2024,1,31)))))

• FILTER narrows down rows to your specified date range
• UNIQUE removes duplicates from the filtered values
• COUNTA counts the remaining unique entries

For older Excel versions (no dynamic arrays) #

Use SUMPRODUCT to mimic deduplication logic. Same column setup as above:

=SUMPRODUCT((A2:A100>=DATE(2024,1,1))*(A2:A100<=DATE(2024,1,31))/(COUNTIF(B2:B100,B2:B100)+(A2:A100<DATE(2024,1,1))+(A2:A100>DATE(2024,1,31))))

• The first part (A2:A100>=...) checks if rows fall within your date range
• The denominator COUNTIF(...) ensures each unique value is counted once; the extra +(A2:A100<...) parts avoid division by zero for rows outside the target range

Your existing rules are solid, but we can tweak them to auto-apply to new rows and refine logic for accuracy:

Fix rule scope to auto-include new rows #

Right now, your rules might be applied to a fixed range (e.g., A2:F100). To make them work for new rows added each week:

1. Open the Conditional Formatting Rules Manager
2. Edit each rule's Applies to range to cover entire columns (e.g., $A:$F) instead of fixed rows

Refine the two core rules #

Rule 1: Highlight row yellow when E column has content

• Formula: =$E1<>"" (the $ locks column E, so it checks every row's E cell)
• Format: Fill color = yellow
• Applies to: $A:$F

Rule 2: Highlight row red when F column date is over 5 workdays old

This needs to exclude weekends (and optionally holidays) using the WORKDAY function:

• Formula: =$F1<>"" && $F1<WORKDAY(TODAY(),-5)
  • $F1<>"" ensures we don't highlight empty rows
  • WORKDAY(TODAY(),-5) calculates the date 5 workdays before today; if $F1 is earlier than this, it means it's been over 5 workdays since the action was logged
• Format: Fill color = red
• Applies to: $A:$F

Weekly update tips #

• Data validation: Add validation to column F to ensure only valid dates are entered (Data > Data Validation > Allow: Date)
• Track follow-ups: Add an optional helper column (e.g., column G: "Followed Up") with a checkbox (Developer > Insert > Check Box) to mark items you've addressed—you can even add a third conditional format to gray out rows where this checkbox is checked

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