用SUM+COUNTIFS计算多列Yes占比（按团队/月份），求更优解法

Got it, let's tackle this messy formula problem and make your Google Sheets workflow way cleaner. Your current approach works but is repetitive and hard to maintain—especially with 8+ sections and 100+ teams. Here are a few elegant solutions tailored to your needs:

方案1：用SUMPRODUCT简化单板块占比计算 #

This replaces your long, repetitive COUNTIFS chain with array logic to batch-count Yes responses across multiple columns in a single section.

公式示例 #

Assumptions:

• $A3 = Team name in the current row of your summary sheet
• $B$1 = Target month (formatted as a date, e.g., 2024-05-01)
• 'Form Responses'!$F$2:$K$1000 = All columns for one section (replace with your actual data range, not full columns!)

=IFERROR(
  SUMPRODUCT(
    ('Form Responses'!$B$2:$B$1000=$A3) *  // Match current team
    ('Form Responses'!$E$2:$E$1000 >= $B$1) *  // Filter by start of month
    ('Form Responses'!$E$2:$E$1000 < EDATE($B$1,1)) *  // Filter by end of month
    ('Form Responses'!$F$2:$K$1000="Yes")  // Count all Yes in the section
  ) /
  (
    COUNTA(FILTER('Form Responses'!$B$2:$B$1000, 'Form Responses'!$B$2:$B$1000=$A3, 'Form Responses'!$E$2:$E$1000 >= $B$1, 'Form Responses'!$E$2:$E$1000 < EDATE($B$1,1))) *
    COLUMNS('Form Responses'!$F$2:$K$1000)  // Auto-calculate number of questions in the section
  ),
  0
)

Why this works better: #

• No more copying COUNTIFS for every column in a section
• COLUMNS() automatically adapts if you add/remove questions in a section
• Using a limited data range (instead of full columns like $B:$B) drastically speeds up calculations for large datasets

方案2：用QUERY一次性生成全团队汇总（推荐 for 100+ teams） #

If you’re dealing with 100+ teams, dragging formulas row-by-row is inefficient. Use QUERY to generate a single, aggregated table with all teams' monthly metrics, then calculate percentages from there.

公式示例 #

This outputs team names, submission counts, and total Yes responses per section for the target month:

=QUERY(
  'Form Responses'!$B$2:$Z$1000,  // Your full response data range
  "SELECT
     B,  // Team column
     COUNT(B),  // Monthly submissions per team
     SUM(IF(F='Yes',1,0)+IF(G='Yes',1,0)+IF(H='Yes',1,0)+IF(I='Yes',1,0)+IF(J='Yes',1,0)+IF(K='Yes',1,0)),  // Section 1 Yes total
     SUM(IF(L='Yes',1,0)+IF(M='Yes',1,0)+IF(N='Yes',1,0)+...),  // Section 2 Yes total (add all columns in the section)
     ...  // Repeat for all 8 sections
   WHERE
     E >= date '"&TEXT($B$1,"yyyy-mm-dd")&"'
     AND E < date '"&TEXT(EDATE($B$1,1),"yyyy-mm-dd")&"'
   GROUP BY B
   LABEL
     B '团队',
     COUNT(B) '当月提交数',
     SUM(...) '板块1Yes总数',
     SUM(...) '板块2Yes总数',
     ...",
  0  // Use 1 if your source data has headers, 0 if not
)

Then calculate percentages in adjacent columns (e.g., for Section 1):

=IFERROR(C2/(B2*6),0)  // Replace 6 with the actual number of questions in Section 1

优势： #

• Generates all team data in one go, reducing calculation load
• Easy to modify sections or add new metrics later
• Sync to other sheets seamlessly with IMPORTRANGE:

  =IMPORTRANGE("Your Summary Sheet ID", "Sheet1!A:Z")
  

  Or filter the imported data with QUERY:

  =QUERY(IMPORTRANGE("Your Summary Sheet ID", "Sheet1!A:Z"), "SELECT * WHERE Col1 <> ''")
  

方案3：动态数组函数（for fully automated updates） #

If you want a hands-off setup that auto-updates when new teams or responses are added, use BYROW + UNIQUE to iterate through all teams and calculate percentages dynamically.

公式示例 #

First, get all unique teams:

=UNIQUE('Form Responses'!$B$2:$B$1000)

Then use BYROW to calculate metrics for each team:

=BYROW(UNIQUE('Form Responses'!$B$2:$B$1000), LAMBDA(team, {
  team,
  // Section 1 percentage
  IFERROR(
    SUMPRODUCT(('Form Responses'!$B$2:$B$1000=team)*('Form Responses'!$E$2:$E$1000>=$B$1)*('Form Responses'!$E$2:$E$1000<EDATE($B$1,1))*('Form Responses'!$F$2:$K$1000="Yes")) /
    (COUNTA(FILTER('Form Responses'!$B$2:$B$1000, 'Form Responses'!$B$2:$B$1000=team, 'Form Responses'!$E$2:$E$1000>=$B$1, 'Form Responses'!$E$2:$E$1000<EDATE($B$1,1)))*COLUMNS('Form Responses'!$F$2:$K$1000)),
    0
  ),
  // Repeat the above block for all 8 sections
  ...
}))

关键注意事项 #

1. Avoid full column references: Always use a limited data range (e.g., $B$2:$B$1000) instead of $B:$B to keep calculations fast.
2. Date formatting: Ensure $B$1 is a standard Google Sheets date, otherwise the QUERY date filters will fail.
3. IMPORTRANGE authorization: You’ll need to grant access the first time you link sheets—after that, sync happens automatically.

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