Search code examples
excelexcel-formulaexcel-2010excel-2007

Excel: Sum values based on values in another column and mapped values

I have the following table

enter image description here

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.

Solution

  • Use this array formula:

    =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})))))))

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

    enter image description here

    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))

    enter image description here

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