Please don't list this on a work's page as a trope. Examples can go on the work's YMMV tab.
We're gonna play fetch. You're the frisbee.
A character who, despite or precisely because of being Nightmare Fuel is also greatly loved by a series' fandom. This can have a variety of reasons. Maybe the character is simply the biggest Badass in the show, or maybe they have a cute, funny or tragic side in addition to their creepy side that endears the character to the fans. The character in question need not be a villain, though a majority of characters this trope applies to are villains or at least antagonists of some sort. This sometimes results in Misaimed Fandom and Draco in Leather Pants.
Note that while these characters are often Nightmare Fuel incarnate, their appeal to non-Nightmare Fetishists is often due to being more appealing (e.g., Magnificent Bastard, The Woobie, etc.) than creepy.
May or may not overlap with Creepy Good.
Sawyer the cleaner. A young woman who needs a electrovoice after having her voice box cut out and works as one-woman Cleanup Crew, using her trusty chainsaw to make unwanted bodies easier to hide by cutting them into pieces ...bodies who aren't necessarily dead when they are brought to her. She also has worked as a hired gun a few times, proving quite adept at using her chainsaw in close combat.
For all his high talk, Ulquiorra wants to be appreciated as such (and maybe even as a Magnificent Bastard, with his Nietzsche WannabeBreaking Lecture attempts), transforming specifically to creep Ichigo out in a hopefully awesome way. He's surprised Ichigo doesn't buy it (while not buying a lot of other shit he spills).
Hollow Ichigo V2 "Ressurection". Devil horns? Devil look? Still awesome, even in previous versions.
And at least during the Rescue Rukia/Soul Society arc, Kenpachi. He kinda moved out of this category after becoming one of the good guys and instead was simply classified as Crazy Awesome instead.
Unohana of all people has become this after the release of Chapter 523.
In Battle Royale, Kiriyama being an emotionless killer doesn't stop fans from liking him.
Hellsing's Alucard, due to being a serious Badass, among other things, as the page image shows. Also, Jan Valentine.
A New Hope has Grand Moff Tarkin. The guy bosses Darth Vader around, and signed the death warrant on an innocent planet out of spite.
Return of the Jedi has The Emperor, the most powerful man in the galaxy who defined Evil Is Cool for an entire generation. A pre-Emperor Palpatine is generally agreed to be one of the better things in the prequel trilogy as well, particularly his memorable hamminess in Revenge of the Sith.
Mapleshade is so crazy that even Tigerstar, the Big Bad, is scared of her, goes on murdering sprees at the slightest provocation, looks like a demon in her official artwork, could be sneaking up on the characters to murder them at any time you read the books because she's invisible to normal characters and nearly-invisible even if you have the power to see her, and is implied to be a cannibal. The fans think she's awesome.
Sol can Mind Rape you by walking nearby, is always completely calm except for one nightmarish moment where he lost his temper, has an insatiable desire for vengeance, and is just creepy in general. These things have made him one of the most unique and well-respected antagonists in the series.
Twilight = James, Victoria, and especially The Volturi.
The First Law has Logen Ninefingers, a barbarian who's famous for being an absolutely insane Blood Knight; even his own men are put off. Glokta has shades of this as well, being quite creepy but also one of the most sympathetic and entertaining characters in the series.
Angelus. When he's Angel he's the regular kind of awesome instead.
Firefly: River Tam, when she's not being a hysterical, crying Woobie that you just want to hug because of what the Alliance did to her brain, can be one of the most frightening people you can ever meet. And she is also awesome, especially in the Big Damn Movie when she Takes A Level In Badass.
The Sarah Connor Chronicles has Cameron. Cold, logical, and frighteningly efficient at what she does, and is scary as hell, but she is without a doubt the most huggable emotionless killer deathbot ever.
The Doctor himself frequently displays unsettling behaviour to remind the audience that he's literally inhuman, but is not less heroic for it. The Fourth Doctor especially, helped along by Tom Baker's terrifying grin, but he's still the most iconic incarnation of the Doctor.
Interviewer: You were creepy in that show, dude! Rami:(good-naturedly) I think I'm creepy in everything.
Captain Ronald Speirs from Band of Brothers, due to him having gunned down several German POWs and shot one of his own men for disobedience. The example works in-universe as well, since men of Easy Company think him awesome yet terrifying.
Lord Varys, "The Spider", from Game of Thrones. Partly helped by the fact that he's actually one of the more principled characters around, in contrast to a few villains; his principles just aren't altruistic.
All of the Lodge Spirits on Twin Peaks edge into this, to various extents. The best example of this is probably The Man From Another Place, since BOB is actually too creepy to be awesome, while The Man is one of the show's most recognizable characters.
Supporting villain Windom Earle edges into this, being a somewhat campy criminal genius with occasional moments of outright horror.
Heavy Metal, as a whole is this to an art form. Sometimes it's the lyrics, and sometimes it's the music itself.
Radiohead's "Climbing Up The Walls". A song written from the perspective of something living in the mind of a Serial Killer, sung in a slurred, eerily distorted voice over heavy guitars, dissonant strings and creepy, abrasive electronics. It's also a fan favorite.
Most of the Industrial genre, in particular the abrasive early acts like Throbbing Gristle and SPK.
Dark Ambient music, especially the subgenre known as Isolationism.
Somehow, Conchita has become a favorite of the fans, despite that she's a psychotic cannibal. Hell, mothy himself has released a huge amount of remixes for her song, and she possesses a character in Story of Evil.
Buckethead. He is a somewhat spooky looking guitarist, with a white mask and some creepy interests and gimmicks. A lot of his music also falls into this category, since he often draws inspiration from the horror genre.
Tom Waits is always awesome, and sometimes creepy.
WWE: "The Boogeyman." He was originally supposed to be a heel, but he became so popular with the fans that WWE had no choice but to turn him face. Didn't stop him from chewing on earthworms and spitting them in his opponents' faces.
WWE: The Undertaker practically runs on creepy awesome, and, even if one says that that is no longer the case, it definitely was in the early to late 90's.
Kane perpetually sits here, whether he's a face or a heel, masked or unmasked. He's worked every creepy aspect imaginable with his character, and nearly every one of them he's worked to perfection.
Jake "The Snake" Roberts plops firmly into this in his heel role, what with the leering, lecherous stare, the fascination with serpents, the happy admittance that he's a lying snake, and his semi-disheveled appearance would put him just as equally in an alleyway wearing nothing but a trenchcoat and sneakers. When he's a face though, he's a different kind of awesome.
Generally, if a Kirby villain fits this trope, it has a good chance of rising above the others in popularity. Zero/Zero Two and Marx Soul are the most prominent examples.
First Encounter Assault Recon: Alma, the oddly, creepily adorable little girl who boils peoples' flesh off, destroys buildings, and rapes the protagonist.
Sovereign from Mass Effect. One conversation with him is one of the most memorable parts of the game.
To a lesser extent, Saren and the Collectors.
GlaDOS's somewhat off personality is what makes Portal the memorable experience it is, turning a puzzle game with a fun gimmick into a storm of memorable quotes. Portal 2 even more so, GlaDOS going all out on the endearingly creepy threats.
Ridley of Metroid. Even the one person who seems a legitimate threat to him, the greatest bounty hunter in the galaxy, who takes on entire planets full of parasitic horrors, is terrified of him. And he's a dragon, which automatically makes him cool.
Albedo, the crazy, regenerating, Evil Albino from Xenosaga. If you didn't get into the series because of KOS-MOS, chances are you got into it because of him.
Creepers, the horrible kamikaze shrubs from Minecraft, are quite popular with the fanbase precisely because they're paranoia-inducing abominations.
Irisu/"Alice", the heroine of Irisu Syndrome. Certainly doesn't come across as such at first glance. She appears to be a meek, quiet girl with a thing for bunnies and who likes cosplaying as a witch/maid. In reality, when she was very young someone killed her pet rabbit, leaving it's decapitated head for her to find topping the pile of gore, which broke something inside her, mentally and spiritually. She's now a drug addicted serial killer who thinks nothing of murdering a group of people so the boy she's stalking will have time to pursue his own psychosis. And, oh, how the fandom loves her for it.
Alice is a small, innocent girl in a blue dress who will happily scamper around highly dangerous locales in search for a friend. She's an undeadHumanoid Abomination gifted with enough raw magical power to murder everything and everyone, and an astounding lack of understanding on exactly why it's not commonly appreciated to torture, kill and eat your friends.
Boethiah sounds like a combination of a crotchety old lady and the general of the army of Hell itself, and has a perpetually-dark shrine high on the side of a mountain which is filled with anarcho-nihilist cultists, huge bonfires, burned corpses, ancient architecture, random blood splatters, and one badass statue.
Cicero is obsessed with his "Mother" (actually the Night Mother, the dead/undead? leader of the Dark Brotherhood), is not above casual murder as per his membership in the Dark Brotherhood, dresses, talks, and dances like an unhinged jester, and is a fan favorite specifically because of that.
The Hydra from Warriors Orochi 3. Not only does it look really intimidating and impressive, but even before the game starts, it slaughters every one of the Dynasty, Samurai, and Original characters aside from a small handful. It's practically invincible until the end game, and all attempts to take it down beforehand will be met with miserable failure.
While the protagonists of the Resident Evil series are rather well-liked in general, perhaps the most distinct character in the series is the Nemesis T-Type from Resident Evil 3: Nemesis. He's tall, imposing, tough-as-nails, extremely persistent and so loved by the fans that he became a bonus character in Ultimate Marvel vs. Capcom 3.
We also have Yukari Yakumo, whom's special ability allows her to open "gaps" into a black void filled with eyes (and in some portrayals, arms that reach out of her gaps). Yukari is also a high-class Reality Warper, bordering on godhood, and The Chessmaster behind almost all major events in Gensoukyou's history, including its creation.
It is hard to describe but this definitely applies to the Slender Man. Mind you, fans still treat him like pure terror but he's also quite popular precisely because of his uniquely subtle, yet terrifying creepiness.
Terezi Pyrope, at least initially. She is probably the single largest Nightmare Fuel Station Attendant in the comic; she was practically introduced by trying to kill John and succeeding in a Bad Future, though she admitted that she was well aware it wouldn't actually work out. Then came Act 5, and with it context and character development aplenty, where it was proved that as trolls go she is probably one of the most pleasant. Even before then, she is one of the most popular characters in the fandom, though that may be more related to her more positive development.
Darth Sidious, noted above, was firmly established as this by Revenge of the Sith where it the only characters that could take him and expect to win were considered the strongest in the Jedi Order, but this series manages to manages to actually elevate him the one time he decides to stop playing Orcus on His Throne and remind everyone why he's the Lord of the Sith and just beneath his league the show's main cast is. He pulls of the Force Choke with just a light gesture on couple of Mooks and pins them against a wall, from the other side! Then he takes on DarthMaul and SavageOpress on his own in an outright Curb-Stomp Battle, reducing two villains that proven to an outright menace against the show's usual protagonist and reduces the fight to Mook Horror Show. It's even made clear before the fight started up he could have just killed them without resorting to using his lightsabers given that he easily pinned and immobilized them (something we had never seen another Force user pull off, let alone against characters that had brought down caves or hurled starfighters), he just fought them hand to hand for fun so he could torture them.
The ever-present, silent, faceless Soundwave. There's a reason everyone but Megatron fears him- he's capable of not only gathering intel from nearly anywhere on Earth, he's an expert at curb-stomping upstarts who disrespect Megatron. He's so cool, he's capable of snarking without even speaking. And when he does speak, he doesn't use his own voice, but instead prefers to Quote Mine the words of others and play them back.