Edwards's famous sermon was incredibly effective if the immediate reaction of his audience is anything to go by. It's no exaggeration to say that many of his auditors were absolutely terrified at his lurid, detailed descriptions of the terrible fate that awaits sinners in the raging flames of hell. It's no wonder that a number of audience members actually fainted in horror.
Even those who managed to remain upright throughout the entire course of Edwards's sermon would have been given much food for thought. Edwards intended to make people think deeply about their lifestyles, whether they were behaving as God-fearing Christians should. He wanted nothing less than to change people's lives for the better, and there's no doubt that many people did indeed heed his word and set themselves on a different path in life.
However, what Edwards had to say wasn't universally accepted. Although just about everyone in America at that time believed in God, not everyone believed that he was the kind of wrathful, vengeful law-giver as presented in the Old Testament. Nor did they believe that hell was a literal place reserved for sinners. That being the case, Edwards's hellfire and brimstone rhetoric would have had no effect on large swathes of the population.
As for myself, Edwards's sermon would've had no effect on me either as his religious beliefs were radically different to mine. (And that's putting it mildly.) Nevertheless, I would still have appreciated his famous sermon as a tour de force of rhetorical brilliance. And that is why, over 250 years later, "Sinners in the Hands of an Angry God" is still pored over, studied, and examined by countless individuals over whom Edwards's Calvinist theology holds no sway.