Google Sheets中SUMIF与SUMIFS日期判断失效问题求解决方案

Hey there! Let's figure out why your second formula isn't working and get you a solid solution.

First, why your original ARRAYFORMULA + SUMIF fails #

Your formula =SUM(ARRAYFORMULA(SUMIF(D2:D100 & F2:F100;">=01.06.2011"&"<=30.07.2011";G2:G100))) has two key issues:

• Incorrect logic: When you concatenate D2:D100 & F2:F100, you're creating a single string per row (like "01.06.201130.07.2011" for one row) and trying to match it against the concatenated criteria string ">=01.06.2011<=30.07.2011". This doesn't check if D is >= the start date and F is <= the end date—it checks if the combined string of D and F matches that specific criteria string, which is never true.
• Date string comparison risks: Using date strings (like "01.06.2011") can lead to unexpected results if your sheet's date storage format doesn't match. Google Sheets stores dates as numeric values under the hood, so string comparisons don't always align with actual date order.

Better Solutions #

Since you already have a working SUMIFS formula, let's start there and cover alternatives if you need array-based logic:

1. Stick with SUMIFS (Simplest & Most Efficient)

Your first formula =SUMIFS(G2:G100;D2:D100;">=01.06.2011";F2:F100;"<=30.07.2011") is already perfect for this use case. SUMIFS is built explicitly for multi-condition summing, so it's cleaner and faster than nested array formulas. To make it even more reliable, replace the date strings with the DATE function to avoid format mismatches:

=SUMIFS(G2:G100; D2:D100; ">="&DATE(2011;6;1); F2:F100; "<="&DATE(2011;7;30))

2. ARRAYFORMULA + IF + SUM (For Dynamic Array Needs)

If you need an array-based approach (e.g., for dynamic row ranges or to output results per row), use logical checks with the DATE function:

=SUM(ARRAYFORMULA(IF((D2:D100 >= DATE(2011;6;1)) * (F2:F100 <= DATE(2011;7;30)); G2:G100; 0)))

• The * acts as an AND operator here—only rows where both conditions are true will return the value from G column, others return 0. Summing this array gives your total.

3. Avoid Forcing SUMIF into Array Logic

While you technically could use SUMIF in an array, it's unnecessary here. SUMIF is designed for single-condition matching, so trying to shoehorn two conditions into it via concatenation breaks the logic. Stick to SUMIFS or the array + IF approach instead.

Key Takeaway #

Always prefer SUMIFS for multi-condition sums—it's Google Sheets' native tool for this job. When working with dates, use the DATE function to ensure consistent, accurate comparisons regardless of your sheet's display format.

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