Google Sheets技术求助：跨工作表指定标题列文本转整数并求和实现

Let's get this sorted out for you! Your original formula had two main issues: incorrect IF function syntax, and hardcoding specific columns (J2, T2) which breaks if your "Impact" columns move or get added to. Here's a robust, dynamic solution that automatically finds all "Impact" columns and calculates the sum correctly.

Correct Formula for Calculator!K2 #

=SUM(
  BYCOL(
    FILTER(Survey!A:ZZ, Survey!1:1="Impact"),
    LAMBDA(col, SWITCH(INDEX(col, ROW()), "Myself", 1, "My team", 5, "Stakeholders", 10, "Project", 15, 0))
  )
)

How This Works: #

• Dynamic Column Filtering: FILTER(Survey!A:ZZ, Survey!1:1="Impact") grabs every column in the Survey sheet where the header in row 1 is exactly "Impact". No more hardcoding column letters!
• Row Matching: INDEX(col, ROW()) pulls the value from the same row as your Calculator!K2 (so if you drag this formula down to K3, it'll use Survey row 3 automatically).
• Text-to-Number Conversion: SWITCH replaces each text value with its corresponding integer:
  • "Myself" → 1
  • "My team" → 5
  • "Stakeholders" → 10
  • "Project" → 15
  • Any other value (including blank) → 0 (so it doesn't affect the sum)
• Sum All Values: SUM adds up all the converted integers from your Impact columns.

Why Your Original Formula Failed #

• The IF function only accepts a single condition and two outcomes (true/false). You tried passing multiple condition-value pairs, which isn't valid syntax.
• Hardcoding Survey!J2 and Survey!T2 means your formula won't pick up new "Impact" columns if you add them later, or if existing ones get moved.

Example Test Case #

If your Survey row 2 has two "Impact" columns with values "Myself" and "Myself", this formula will return 1 + 1 = 2—exactly what you need!

内容的提问来源于stack exchange，提问作者Patricia Beate Lulle