如何用公式统计指定月份同背景色条目数及对应列求和？

Nice problem to solve! The catch here is that Google Sheets' standard functions like COUNTIF or SUMIF can't directly access cell formatting (like background colors)—they only work with cell values. To pull this off, we'll use a custom Apps Script function to read background colors, then pair it with your existing month-checking logic.

Step 1: Build the Custom Background Color Function #

First, we need a small script to grab the background color of each cell in your target range:

1. Open your Google Sheet, go to Extensions > Apps Script to launch the script editor.
2. Delete any default code in the editor, then paste this function:

function getBackgroundColor(range) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet();
  const rangeObj = sheet.getRange(range);
  const backgrounds = rangeObj.getBackgrounds();
  // Flatten the 2D array to match the structure of your single-column range
  return backgrounds.map(row => row[0]);
}

3. Click the save icon (💾), name the project something like "BackgroundColorTools", then close the script editor.

Step 2: Count June Entries with Red Background #

Use this formula (replace 'SHEET NAME' with your actual sheet name). It combines COUNTIFS to check both the month (June = 6) and the red background color (hex code #ff0000—adjust this if your red uses a different hex value):

=ArrayFormula(COUNTIFS(MONTH('SHEET NAME'!W2:W), 6, getBackgroundColor('SHEET NAME'!W2:W), "#ff0000"))

Step 3: Sum Column X Values for June + Red Background #

To sum the corresponding values in column X, use SUMIFS with the same dual conditions:

=ArrayFormula(SUMIFS('SHEET NAME'!X2:X, MONTH('SHEET NAME'!W2:W), 6, getBackgroundColor('SHEET NAME'!W2:W), "#ff0000"))

Quick Notes: #

• If your red background uses a different hex code (e.g., a darker red like #cc0000), find the exact code by selecting a red cell, going to Fill color > Custom, and copying the hex value from the color picker.
• This function works for both manually set background colors and colors applied via conditional formatting, since getBackgrounds() reads the actual displayed color of each cell.
• If you update cell background colors later, refresh the formulas by pressing Ctrl+R (Windows) or Cmd+R (Mac), or re-open the sheet.

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