请求协助：按代理及日期范围统计A列唯一Item ID的公式

Hey there! Let's figure out the right Google Sheets formulas to count unique Item IDs by agent within a specific date range. Here are two reliable methods depending on your needs:

Method 1: COUNTUNIQUEIFS (Great for Single-Agent Counts) #

If you just need to calculate the unique Item IDs for one specific agent and date range, this function is straightforward. Let's assume:

• Item IDs are in column A
• Agent names are in column B
• Dates are in column C

Use this formula (replace placeholders with your actual values):

=COUNTUNIQUEIFS(A:A, B:B, "Your Target Agent", C:C, ">="&DATE(2024,1,1), C:C, "<="&DATE(2024,12,31))

What each part does:

• A:A: The column containing all Item IDs we want to count uniquely
• B:B, "Your Target Agent": Filters rows to only include entries for the agent you specify (swap the text for a cell reference like E2 if you want to pull the agent name from another cell)
• C:C, ">="&DATE(2024,1,1): Sets the start of your date range (adjust year/month/day to your needs)
• C:C, "<="&DATE(2024,12,31): Sets the end of your date range

Method 2: QUERY (Perfect for Dynamic Multi-Agent Reports) #

If you want a full breakdown of unique Item IDs for every agent in your date range (all in one table), the QUERY function is way more flexible. Using the same column assumptions as above:

=QUERY(A:C, "SELECT B, COUNTUNIQUE(A) WHERE C >= date '2024-01-01' AND C <= date '2024-12-31' GROUP BY B LABEL B 'Agent', COUNTUNIQUE(A) 'Unique Item IDs'", 1)

Breakdown of the query:

• A:C: The full range of your data (expand this if your columns are spread out more)
• SELECT B, COUNTUNIQUE(A): Picks the agent column and calculates unique Item IDs from column A
• WHERE C >= date '2024-01-01' AND C <= date '2024-12-31': Filters rows to your desired date range (use ISO format YYYY-MM-DD for dates here)
• GROUP BY B: Groups results so each agent gets their own row with their unique count
• LABEL ...: Renames the output columns to make the report easier to read
• 1: Tells Google Sheets your data has a header row (remove this number if your sheet doesn't have headers)

Pro Tips: #

• To use cell references for dynamic dates (e.g., start date in cell E3, end date in F3), adjust the formulas:
  • For COUNTUNIQUEIFS:

    =COUNTUNIQUEIFS(A:A, B:B, E2, C:C, ">="&E3, C:C, "<="&F3)
    

  • For QUERY:

    =QUERY(A:C, "SELECT B, COUNTUNIQUE(A) WHERE C >= date '"&TEXT(E3, "yyyy-mm-dd")&"' AND C <= date '"&TEXT(F3, "yyyy-mm-dd")&"' GROUP BY B LABEL B 'Agent', COUNTUNIQUE(A) 'Unique Item IDs'", 1)
    

• Make sure your date column is formatted as a date in Google Sheets (not plain text) for these formulas to work correctly.

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