STUDENT TIME SCORE WANT
JOHN 1 68 146
JOHN 2 78 146
JOHN 3 77 146
JOHN 4 91 146
JOHN 5 96 146
JAMES 1 66 119
JAMES 2 53 119
JAMES 3 80 119
JAMES 4 96 119
JAMES 5 50 119
JAMES 6 94 119
I have data COLUMNS 'STUDENT' AND 'TIME' AND 'SCORE' and wish to create 'WANT' and the rule which for I will need VLOOKUP is this: WANT = the sum of the SCORE values at TIMES 1 and 2, so I WISH TO USE VLOOKUP to find the 'SCORE' values for each 'STUDENT' at TIMES 1 and 2 and take the sum.
CodePudding user response:
Assuming your dataset is ordered by "student name" (with unique student names), then "time", you could use :
Classical way, in F2:
=IF(AND(B2=1,B3=2,A2=A3),C2 C3,IF(AND(B2=2,B1=1,A2=A1),C2 C1,OFFSET($F$1,MATCH(A2,A$2:A2,0),0)))
Greedy way (Office365 needed), in F2 :
=VLOOKUP(A2,FILTER($A$2:$C$12,$B$2:$B$12=1),3,FALSE) VLOOKUP(A2,FILTER($A$2:$C$12,$B$2:$B$12=2),3,FALSE)
CodePudding user response:
You can try SUMIFS()
in this way.
=SUM(SUMIFS($C$2:$C$12,$B$2:$B$12,{1,2},$A$2:$A$12,A2))
It may need to array entry for older versions of excel. Array entry by CTRL SHIFT ENTER.