This is an easy one. Templar and Dark Templar from Starcraft.
First, Templar. Their uses are far from obvious when you first start playing the game because when you just start out everything's "direct damage or nothing at all." But then you get a little bit more time with the game and you start realizing that spellcaster units do more than just go invisible and you get the idea in your head that an armada of carriers suddenly becomes all that more terrifying when you hallucinate about 30 more into existence. Or how about when you suddenly see a fleet of battlecruisers on the horizon and while they're busy picking off dragoons, a flock of Templar harry in and psi storm the whole lot of baddies into rubble? That sort of satisfaction can only be derived from a presumably useless unit (and yes, I admit that I know very, very little of high/pro-level SC, but we're talking about MY favorite units and even though I suck they are MY favorites).
Dark Templar are the exact opposite side of that coin with the exception of also being painfully slow-moving. They're invisible by default and have little more going for them than the fact that they can reel off insane amounts of damage. Oh wait, that's awesome.
On top of that, and I know I'm technically getting into another unit here, but two Dark Templar can fuse into a Dark Archon, which is a spellcaster unit (in stark contrast to the fused Templar becoming an Archon, which is a direct-damage unit) and is decidedly not invisible. So what does the Dark Archon have going for him? Oh, I don't know, how about the power of PARALYZING EVERYTHING? And, unlike the Arbiter, you can take your time and relish with sick glee the shit-kicking of an unresponsive opponent exceeding the worst and most boring of WWE house shows.
Just my thing, I guess. SC is in so many ways a game so much better than I deserve, what with its endless depth and replayability, because I just don't have the time nor attention span to really master it--or even get good--but it's a fun game to mess with even for the casual because there's so much to be discovered even with only a passing glance.