如何根据单元格文本匹配难度，对标记完成的任务行求和？

Got it, let’s break down how to get this working exactly as you need. The goal is to have cell A2 automatically sum the weights of completed tasks, based on the difficulty selected in A1. Here's the formula you can drop straight into A2:

=SUMPRODUCT((B4:J4="c")*INDEX(B1:J3,MATCH(A1,{"easy","medium","hard"},0),0))

Let’s walk through what each part does: #

• MATCH(A1,{"easy","medium","hard"},0): This checks what’s in A1 and returns 1 for "easy", 2 for "medium", or 3 for "hard"—perfect for targeting the right row of weights.
• INDEX(B1:J3,[result from MATCH],0): This pulls the entire row of weights corresponding to your selected difficulty (e.g., if A1 is "medium", it grabs B2:J2).
• (B4:J4="c"): Creates a TRUE/FALSE array where TRUE marks cells in row 4 that have "c" (completed tasks). When multiplied, TRUE becomes 1 and FALSE becomes 0, so only completed tasks contribute to the sum.
• SUMPRODUCT: Multiplies the two arrays together (weight row × completed task markers) and adds up the results—giving you the total score for completed tasks of the selected difficulty.

Bonus: Handle invalid difficulty inputs #

If you want to avoid a #N/A error if someone types something other than "easy"/"medium"/"hard" in A1, wrap the formula in IFERROR:

=IFERROR(SUMPRODUCT((B4:J4="c")*INDEX(B1:J3,MATCH(A1,{"easy","medium","hard"},0),0)),"Invalid difficulty")

Example scenario: #

If A1 is set to "hard", and cells D4 and G4 are marked "c", A2 will automatically calculate the sum of D3 and G3—exactly what you’re looking for.

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