If you just handed in your resignation, without first trying (hard enough) to resolve the problems that resulted in you deciding to leave, then it's entirely possible (although not particularly likely) that accepting a counter-offer would work out.
Just make sure that you find permanent solutions to your problems, and these aren't promises of things that will happen down the line (which are easy to neglect tending to) but have immediate impact. For example:
If it's a problem with someone you're working with, the preferred solution would be not working with them at all any more - if they try to improve, it could take quite some time, and it's likely that they'd fall back into old habits. Although I'd say if you have a problem with someone severe enough to resign over, the real problem may lie with you, or the company (for allowing the behaviour, meaning you may actually have a problem with the company culture, see below).
If it's environmental (too much or little sunlight, too many cats, etc.), a permanent solution could very well be found, although if you don't see eye-to-eye with your coworkers on this issue, you may find yourself fighting this same battle over and over again (even if the solution may seem permanent at the time) - it's also entirely possible that your coworkers are also severely unhappy with these issues, which could make your actions have the benefit of making them happy as well.
If it's about the culture, politics or whatever, these things are usually ingrained into the company and you shouldn't expect it to change, regardless of any promises. You could find a solution that limits your exposure to this, but this is probably temporary - if you want to move up in the company, you'll likely run into the same things.
If it's about money, it's probably best to just move on - this "give me more money or I'll leave" idea will likely stick around and that will be good for neither you nor the company.
It should go without saying that you should find solutions to all or an acceptable amount of your problems. All problems should either be fixed or you should be willing to deal with them for at least a few years. If you decide to stay, they could ignore any remaining problems given that there's no immediate threat of you leaving any more.
Note that accepting a counter-offer is not the preferred way of doing things. They may very well end up thinking that you'll probably leave soon anyway (given that this is what usually happens), so you end up not getting any exciting or impactful projects to work on, less or no promotions or raises and may even find yourself worked out of the company all together (fired) down the line.
Thus, I'd say it's always a better option to first discuss the reasons you'd consider leaving with your manager. Be sure to make it clear how severe these issues are (or they may go ignored), however, try to avoid explicitly mentioning that you're considering resigning (although it's fine if it's implied) - that could easily sound like a threat to get what you want, which is not good (I think statements along the line of "I'm not sure I could go on like this any more" or "This is severely impacting my personal life", obviously with a lot of details pertaining to your specific situation added, could portray just the right amount of seriousness without being over the line, but experiences may vary).
Given that money is often the reason for moving, I thought I'd explicitly point this out - don't discard the idea of asking your manager for a raise if you otherwise like your job (although perhaps not if you need a massive percentage to catch up to the industry standard). It's a perfectly acceptable thing to do. Be sure to look up tips online for doing this efficiently.
