This may seem easy, but I can’t find the formula to calculate the total score of a student in the table shown below.
I tried to put something like this :
=SUMPRODUCT((B6:T6="X");B5:T5)
(I first removed the word "POINTS" in cells B5 to T5 in order to keep only the numerical coefficients).
It seems like the part (B6:T6="X") doesn’t return a matrix as I want it to. In Excel 2019, I heard this is called "Spilling", but my computer only has Excel 2016 and I’m looking for another solution.
Thanks for your time!
>Solution :
Close, but you need to do the — trick to convert the Trues and Falses into 1s and 0s! Try this
=SUMPRODUCT(–(B6:T6="x"),$B$5:$T$5)