I've seen YouTubers get scared over Phasmophobia. I'd recommend watching Markiplier's lately video on Phasmophobia, where he plays with two of his friends and one friend of his stays in the van while Markiplier and his second friend go into the mansion. It gets extremely scary that way, when players take the game seriously and one of the players stay in the van.
To answer the question presented, yes, multiplayer horror games can be scary. Some people won't get scared while others will, it depends on the individual and the game they are playing.