If you select D3 & use the evaluate button on the formula tab do you see this as the last step

View attachment 19032

All steps are listed below:

SUMPRODUCT(($A$2:$A$5=A3)*IFERROR(VLOOKUP($B$2:$C$5,{"Eight",8;"Six",6},2,0),0))

SUMPRODUCT(({"NAME1";"NAME2";"NAME1";"NAME3"}="NAME2")*IFERROR(VLOOKUP($B$2:$C$5,{"Eight",8;"Six",6},2,0),0))

SUMPRODUCT(({FALSE;TRUE;FALSE;FALSE})*IFERROR(VLOOKUP($B$2:$C$5,{"Eight",8;"Six",6},2,0),0))

SUMPRODUCT(({FALSE;TRUE;FALSE;FALSE})*IFERROR(VLOOKUP("EIGHT",{"Eight",8;"Six",6},2,0),0))

SUMPRODUCT(({FALSE;TRUE;FALSE;FALSE})*IFERROR(8,0))

SUMPRODUCT(({FALSE;TRUE;FALSE;FALSE})*8)

SUMPRODUCT(({0;8;0;0})

8