求助：Excel中SUMPRODUCT函数动态列引用的实现

Alright, let's tweak your SUMPRODUCT formula to use that dynamic column reference tied to the month in cell U2. Here's exactly how to make it work:

Updated Formula #

=SUMPRODUCT(
    ('One Pager Grid'!B10:B1000='SCO One Pager'!A49)*
    ('One Pager Grid'!D10:D1000='SCO One Pager'!B49)*
    INDEX('One Pager Grid'!$10:$1000, , MATCH(U2,'One Pager Grid'!$9:$9,0))
)

Breakdown of the Changes #

Let's walk through what each part does:

• MATCH(U2,'One Pager Grid'!$9:$9,0): This finds the exact column number where your target month (stored in U2, like "JAN") lives in the header row (row 9 of 'One Pager Grid'). The 0 ensures an exact match—critical since your header uses specific month codes.
• INDEX('One Pager Grid'!$10:$1000, , [column number]): The INDEX function converts the column number from MATCH into a full range of rows 10-1000 in that column. This replaces your fixed 'One Pager Grid'!S10:S1000 reference with one that updates automatically when U2 changes.
• Your original conditional checks for columns B and D stay intact—they still filter rows to only those matching your criteria in 'SCO One Pager'!A49 and B49.

Key Tips to Avoid Issues #

• Exact Matches Only: Double-check that the text in U2 matches the header text in row 9 perfectly (same capitalization, no extra spaces). A mismatch will throw a #N/A error.
• Lock References: The $ signs in $9:$9 and $10:$1000 prevent the header row and data range from shifting if you ever copy or drag the formula to other cells.
• Optional Error Handling: If you want to avoid errors when U2 has a non-matching value, wrap the MATCH in IFERROR:

  INDEX('One Pager Grid'!$10:$1000, , IFERROR(MATCH(U2,'One Pager Grid'!$9:$9,0),0))
  

  This returns 0 for invalid months, so SUMPRODUCT will output 0 instead of an error.

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