Google Sheets：如何基于不同下拉筛选器在QUERY公式中实现列求和

Hey there! I totally get how frustrating it is when your sum formula ignores your dropdown filters—let's get this sorted out for you. The issue you're seeing is because your current sum formula is calculating the entire column, not just the rows that match your selected school and date range. Here's how to tie your sum directly to those filters so it updates automatically:

First, Let's Recap Your Setup (Adjust as Needed) #

I’ll assume you have:

• A dropdown for school selection in cell D1
• Start date dropdown in E1
• End date dropdown in F1
• Your main QUERY formula pulling filtered data from the "school data" tab (e.g., starting at cell A5)

Step 1: Update Your Data Extraction QUERY (If Needed) #

Make sure your base QUERY uses the dropdown values correctly (this handles special characters like single quotes in school names too):

=QUERY('school data'!A:C, "SELECT * WHERE A = '"&SUBSTITUTE(D1,"'","''")&"' AND B >= date '"&TEXT(E1,"yyyy-mm-dd")&"' AND B <= date '"&TEXT(F1,"yyyy-mm-dd")&"'", 1)

• Replace A:C with your actual data range in "school data"
• A = your school name column, B = your date column
• The 1 at the end tells QUERY your source data has a header row

Step 2: Add Dynamic Sum Formulas #

Instead of using a basic SUM() function, use a QUERY that reuses the exact same filter conditions as your data extraction. This ensures the sum only includes rows matching your selected school and date range.

For example, if you want to sum values in column C (from "school data"):

=QUERY('school data'!A:C, "SELECT SUM(C) WHERE A = '"&SUBSTITUTE(D1,"'","''")&"' AND B >= date '"&TEXT(E1,"yyyy-mm-dd")&"' AND B <= date '"&TEXT(F1,"yyyy-mm-dd")&"' LABEL SUM(C) ''", 1)

• SUM(C) targets the column you want to sum
• LABEL SUM(C) '' removes the default "sum" header so only the number shows up
• Place this formula directly below your filtered data in the corresponding column (e.g., if your filtered data ends at row 20, put the sum in row 21)

Key Tips to Avoid Issues #

• Date Formatting: The TEXT(E1,"yyyy-mm-dd") part converts your dropdown date to the format QUERY understands—don’t skip this, or date filters might break.
• Special Characters: The SUBSTITUTE(D1,"'","''") fixes errors if your school names have single quotes (e.g., "O'Neil High").
• Match Columns: Double-check that the column letters (A, B, C) in your sum QUERY match the ones in your data extraction QUERY.

Now when you change your school or date range dropdowns, both your filtered data and the bottom sums will update instantly!

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