Excel中：根据另一列值和映射值和值(Excel: Sum values based on val

I have the following table

On each day a single sport activity is organized and attending students receive bonus points. Students can attend activities each day to receive points.

Based on existing columns I need to calculate total bonus points for each student (column I)

If in column D I had number of points for that day I could solve this with a SUMIF but as that is not the case I am not sure how to incorporate values from F which I need for summing.

Any help would be greatly appreciated.

Answer 1:

使用该阵列式：

=SUM(SUMIFS(F:F,E:E,INDEX(C:C,N(IF(1,MODE.MULT(IF(ISNUMBER(SEARCH("," & H2 & ",","," & $B$2:$B$7 & ",")),ROW($B$2:$B$7)*{1,1})))))))

作为它需要被确认的阵列式Ctrl键移输入，而不是退出编辑模式时输入。

---

编辑的基础上，TomSharpes但SUMIFS而不是抵消。

=SUMPRODUCT(ISNUMBER(SEARCH("," & H2 & ",","," & $B$2:$B$7 & ","))*SUMIFS($F$2:$F$4,$E$2:$E$4,$C$2:$C$7))

注：我使用的搜索删除的情况下匹配和括号的输入和检查用,所以Ann未发现Anne

Answer 2:

=SUMPRODUCT(ISNUMBER(FIND(H2,$B$2:$B$7))*N(OFFSET($F$1,MATCH($C$2:$C$7,$E$2:$E$4,0),0)))

文章来源: Excel: Sum values based on values in another column and mapped values