I really enjoy playing challenging games multiplayer (typically coop, and the rare game where you need to specialize, like PlanetSide or any of the large Battlefield games), because it usually requires strategies, tactics, role distribution and teamwork. If you succeed, you will really feel like you accomplished something together. If you fail, you can attempt to think up a new plan and go with that. Either way, it's fun in my eyes.
Unfortunately, most games nowadays take the singleplayer content and tack on a networked mode. This usually results in something meant to be done alone being unchallenging and pretty easy when working together. If not, it's pretty much always competitive gameplay without any required specialization (as in needed to be effective, not personal taste. Think anti-tank soldier or medic, or a PlanetSide hacker.) I enjoy any role pretty well as long as I'm useful, so personally such games are best for me.
Best times I had in multiplayer was Faces of War 4-player co-op. Most of the missions were commando-sized, so you'd typically have a squad of 4 guys (sometimes more guys and maybe a vehicle). We always had someone find a sniper rifle off a dead enemy, one guy running around with an anti-tank weapon, or, if we had more than 4, people crewing anti-tank guns and mortars whenever it was necessary. That game was fantastic. Not in the least because it was an incredibly adaptive tactics game. You could easily control your units with keyboard shortcuts, or go into Direct Control mode with one guy or vehicle and determine where to fire, what ammo to load, what your soldier should loot (the game kept track of every unit's inventory), where to throw grenades, you name it. Hell, it even had mine-laying and controllable coastal guns.
Enough about that though. Point is, dedicated multiplayer can make a game so much more enjoyable. Cooperative play just becomes so much deeper and more interesting with actual human intelligence by your side.