如何统计指定日期范围内各车队编号的日期出现频次？

Got it, let's tweak your existing array formula to include a date range constraint—this is straightforward once you know where to slot in the extra logic.

Your Original Formula (Recap) #

You’re using this to count unique dates tied to a specific fleet ID (in cell A6):

=SUM(IF(FREQUENCY(IF('Management Ops Sheet'!$B$8:$B$500=A6,'Management Ops Sheet'!$A$8:$A$500),'Management Ops Sheet'!$A$8:$A$500)>0,1))

It works by filtering dates for the target fleet, using FREQUENCY to identify unique dates, then summing up how many distinct entries exist.

Modified Formula with Date Range #

To restrict the count to dates between a start and end date (replace StartDate and EndDate with your actual cell references, e.g., $C$2 and $D$2), use this:

=SUM(IF(FREQUENCY(IF(('Management Ops Sheet'!$B$8:$B$500=A6)*('Management Ops Sheet'!$A$8:$A$500>=StartDate)*('Management Ops Sheet'!$A$8:$A$500<=EndDate),'Management Ops Sheet'!$A$8:$A$500),'Management Ops Sheet'!$A$8:$A$500)>0,1))

• Key Additions: The * operators act as "AND" logic here. We’re now filtering for three conditions at once:
  1. Fleet ID matches the value in A6
  2. Date falls on or after your specified start date
  3. Date falls on or before your specified end date
• Quick note: For older Excel versions, enter this as an array formula by pressing Ctrl+Shift+Enter after typing. Excel 365/2021 handles array formulas automatically.

Simplified Alternative (Excel 365/2021+) #

If you’re on a newer Excel version, you can use a more readable formula with modern functions:

=COUNTA(UNIQUE(FILTER('Management Ops Sheet'!$A$8:$A$500,('Management Ops Sheet'!$B$8:$B$500=A6)*('Management Ops Sheet'!$A$8:$A$500>=$C$2)*('Management Ops Sheet'!$A$8:$A$500<=$D$2))))

This breaks down to:

1. FILTER the date column to only include rows matching your fleet ID and date range
2. UNIQUE removes duplicate dates from the filtered list
3. COUNTA counts the number of unique dates remaining

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