Excel:基于另一列中的值和映射的值求和的值 [英] Excel: Sum values based on values in another column and mapped values

问题描述

我有下表

每天组织一次体育活动，参加活动的学生将获得积分.学生每天可以参加活动以获得积分.

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)

如果在D列中那天我有积分，我可以用SUMIF来解决这个问题，但是由于不是这种情况，我不确定如何将F中需要求和的值纳入其中.

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.

任何帮助将不胜感激.

推荐答案

使用此数组公式:

=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-Shift-Enter而不是Enter进行确认.

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

编辑，基于TomSharpes，但使用SUMIFS而不是OFFSET.

Edit, based on TomSharpes but with SUMIFS instead of OFFSET.

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

注意:我使用SEARCH删除大小写匹配，并用,将输入括起来并进行检查，以便在Anne

Note: I used SEARCH to remove the case match and bracketed the inputs and checks with , so Ann is not found in Anne

这篇关于Excel:基于另一列中的值和映射的值求和的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！