Google Sheets多查询单公式运行异常及提速优化求助

Hey there, let's tackle this slow Google Sheets formula issue together! Repeating SUMIFS 31 times for each day of the month creates unnecessary calculation overhead—here are practical, actionable tweaks to speed things up:

1. Replace Repeated SUMIFS with ARRAYFORMULA #

Instead of writing a separate SUMIFS for each date, use ARRAYFORMULA to calculate all daily totals in one go. This slashes the number of individual formulas Google Sheets needs to process.

For example, if your target dates are in Sheet2!A2:A32 (one per day of the month), and you're summing values from Data!D:D with matching dates and other conditions, rewrite it like this:

=ARRAYFORMULA(
  IF(Sheet2!A2:A32="",,
    SUMIFS(
      Data!D:D,
      Data!A:A, Sheet2!A2:A32,
      Data!B:B, "Specific Condition",
      Data!C:C, "Another Condition"
    )
  )
)

The ARRAYFORMULA automatically applies the SUMIFS to every date in the range—no more copy-pasting the formula 31 times.

2. Preprocess Data with QUERY to Reduce Scope #

If your SUMIFS is scanning huge datasets repeatedly, use QUERY to filter down the data first to only what's needed for the month. This cuts down on the number of cells Google Sheets has to evaluate for each calculation.

For example, create a helper range (or use it directly in your formula) that filters the dataset to the current month:

=QUERY(Data!A:D, "SELECT A,B,C,D WHERE A >= date '"&TEXT(Sheet2!A2, "yyyy-MM-dd")&"' AND A <= date '"&TEXT(EOMONTH(Sheet2!A2,0), "yyyy-MM-dd")&"'", 1)

Then reference this filtered range in your SUMIFS or ARRAYFORMULA—it’ll be much faster since it only includes relevant rows.

3. Switch to SUMPRODUCT for Array-Based Summing #

SUMPRODUCT handles multiple conditions in a single array operation, which is more efficient than repeating SUMIFS. It works by multiplying boolean arrays (where conditions are met) against the values you want to sum.

Here’s an example for daily totals:

=ARRAYFORMULA(
  SUMPRODUCT(
    (Data!A:A=Sheet2!A2:A32)*
    (Data!B:B="Specific Condition")*
    (Data!C:C="Another Condition")*
    Data!D:D
  )
)

Note: If your dataset is extremely large, test this against ARRAYFORMULA+SUMIFS to see which performs better—both are way better than 31 separate formulas.

4. Use a Pivot Table Instead of Formulas #

Google Sheets’ pivot tables are optimized for aggregation tasks like daily sums. They’ll almost always outperform manual formulas for this kind of work, especially with large datasets.

• Select your raw data range
• Go to Data > Pivot table
• In the pivot table editor:
  • Add your date field to the Rows section
  • Add the value field you want to sum to the Values section (set to SUM)
  • Add any other filter conditions to the Filters section
    Pivot tables refresh quickly and avoid the overhead of repeated formula calculations.

5. Narrow Down Cell Ranges #

Avoid referencing entire columns (like A:A) in your formulas—this forces Google Sheets to scan every cell in the column, even empty ones. Instead, use specific ranges (e.g., A2:A1000) that match the size of your actual data.

You can also define named ranges for your data sources (via Data > Named ranges) to make formulas cleaner and ensure Google Sheets only evaluates the necessary cells.

6. Adjust Calculation Settings #

If your sheet is recalculating constantly, tweak the calculation mode to reduce unnecessary processing:

• Go to File > Settings > Calculation
• Switch from "On change" to "On change and every minute" or "Manual" (if you don’t need real-time updates)
• For manual mode, refresh calculations manually with Ctrl+R (Windows) or Cmd+R (Mac)

Try these steps one by one—starting with ARRAYFORMULA or a pivot table will likely give you the biggest performance jump right away.

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