COUNTIFS函数日期范围语法疑问：跨工作表统计双条件产品数量

Absolutely, COUNTIFS is the perfect function for this task—it’s built to handle multiple criteria, including date ranges. The key is nailing the date syntax, which is where most folks run into snags. Let’s walk through how to make this work, especially since your data lives in another worksheet.

Basic Formula Structure #

Assuming your data is in a sheet named ProductData, with dates in column A and colors in column B, here’s how to count red products between March 1, 2018, and March 31, 2018:

=COUNTIFS(ProductData!A:A, ">="&DATE(2018,3,1), ProductData!A:A, "<="&DATE(2018,3,31), ProductData!B:B, "Red")

Breaking Down the Formula #

Let’s unpack each part to understand why it works:

• ProductData!A:A: References the date column in your external worksheet. If the sheet name has spaces, wrap it in single quotes (e.g., 'Sales Data'!A:A).
• ">="&DATE(2018,3,1): The first date condition—counts dates on or after March 1, 2018. Using the DATE() function ensures Excel recognizes the value as a date (instead of text), avoiding formatting conflicts.
• ProductData!A:A: We reuse the date column for the second range condition.
• "<="&DATE(2018,3,31): Counts dates on or before March 31, 2018.
• ProductData!B:B: References the color column in the external sheet.
• "Red": The color criteria we’re filtering for.

Using Cell References for Dates #

If you want a dynamic formula (so you can update start/end dates without editing the formula itself), reference cells containing your date values. For example, if cell D1 holds your start date and D2 holds your end date:

=COUNTIFS(ProductData!A:A, ">="&D1, ProductData!A:A, "<="&D2, ProductData!B:B, "Red")

Common Mistakes to Avoid #

• Don’t hardcode dates as text: Typing ">=1 Mar 18" might work occasionally, but it’s unreliable if Excel’s date format settings differ. Stick to DATE() or cell references.
• Forgetting the concatenation operator (&): You need to link the comparison operator (>=, <=) to the date value—without &, Excel will treat the whole thing as a text string and fail to evaluate it correctly.
• Incorrect sheet references: Double-check the sheet name spelling, and use single quotes around names with spaces (e.g., 'Q1 Sales'!A:A).

内容的提问来源于stack exchange，提问作者D. Tunus