Playsound in the position of the mob that executes?

You are executing the command at every player online as the creeper. That is most likely causing the problem. To fix this use the following command.

execute as @a at @s run execute as @e[type=minecraft:creeper, name=red, distance=..10] at @s run playsound minecraft:entity.creeper.primed hostile @a[distance=..10] ~ ~ ~

This command runs the whole second execute command as the players at the players which runs the play sound command as and at the creepers in range of the players targeted at the players in range. Your command runs the playsound command at the players targeted at the players. I have not tested this but this seems to be what you are trying to accomplish. If this doesn't work check the syntax of the playsound command. Check as well if the sound is a actual sound. I hope this helps.