如何编写公式统计指定客户且日期在一年内的行数？

Absolutely! I’ll walk you through how to do this in the most common tools—Excel/Google Sheets and SQL—since you didn’t specify which platform you’re working with.

Excel or Google Sheets #

The go-to function here is COUNTIFS, which lets you count rows that meet multiple criteria. Let’s say:

• Your dates are in column A:A
• Your customer names are in column B:B
• You want to count rows for the customer "Acme Corp" where the date is within the last rolling year (from today, back 365 days)

Rolling Year Formula #

=COUNTIFS(A:A, ">="&TODAY()-365, A:A, "<="&TODAY(), B:B, "Acme Corp")

Let’s break this down:

• A:A, ">="&TODAY()-365: Checks if the date is at least 365 days ago (start of the 1-year window)
• A:A, "<="&TODAY(): Ensures we don’t include any future dates
• B:B, "Acme Corp": Matches only rows where the customer name exactly matches your specified value

Natural Year (Calendar Year) Formula #

If you want to count rows for the current calendar year (instead of a rolling 12 months), use this instead:

=COUNTIFS(A:A, ">="&DATE(YEAR(TODAY()),1,1), A:A, "<="&DATE(YEAR(TODAY()),12,31), B:B, "Acme Corp")

This targets all dates from January 1 to December 31 of the current year.

SQL #

If you’re working with a database, the approach depends on your SQL dialect, but the core logic is the same: filter for dates in the last year and the specific customer, then count the rows.

Rolling Year Query (Standard SQL) #

SELECT COUNT(*) AS matching_rows
FROM orders  -- Replace with your table name
WHERE 
  order_date >= CURRENT_DATE - INTERVAL '1 year'  -- Replace order_date with your date column
  AND customer_name = 'Acme Corp';  -- Replace with your customer column and target name

Note: Some databases use slightly different syntax for date intervals:

• MySQL: DATE_SUB(CURDATE(), INTERVAL 1 YEAR) instead of CURRENT_DATE - INTERVAL '1 year'
• SQL Server: DATEADD(year, -1, GETDATE())
• Oracle: SYSDATE - INTERVAL '1' YEAR or ADD_MONTHS(SYSDATE, -12)

Natural Year Query (Calendar Year) #

For the current calendar year, this is more efficient (avoids applying functions to your date column, which can skip indexes):

SELECT COUNT(*) AS matching_rows
FROM orders
WHERE 
  order_date >= DATE_TRUNC('year', CURRENT_DATE)
  AND order_date < DATE_TRUNC('year', CURRENT_DATE) + INTERVAL '1 year'
  AND customer_name = 'Acme Corp';

If you’re using a different tool (like Python’s Pandas, for example), just let me know and I can share that approach too!

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