Excel: what would be the formula for adding numbers into vote count everytime a name is mentioned un-CodePudding

For example if a show is written under Column b which is equivalent to 3 points it should add 3 into the vote count column.

CodePudding user response：

Using SUMPRODUCT()

• Formula used in cell F2

=SUMPRODUCT((E2=$A$2:$C$18)*(E2<>"")*TEXTAFTER(TEXTBEFORE($A$1:$C$1," "),"("))

Fill Down for rest of the cells !

With Single Array formula that Spills hence no need to drag,

• Formula used in cell F2

=MAP(E2:E18,LAMBDA(x,SUMPRODUCT((x=$A$2:$C$18)*(x<>"")*TEXTAFTER(TEXTBEFORE($A$1:$C$1," "),"("))))