I hear a lot of advice saying to never take a counter offer when leaving a company. Does anyone have an experience where it has worked out?

Worked for me a bit ago... I was transferring internally to a different development team and they countered with more than the other team could offer due to HR policies. I ended up staying.

I think the only way it could work is if you were to change up your environment/job responsibilities. A.K.A. get a new job. Most of the time when you've already expressed you're unhappy at a place, more money usually won't make you more happy. Most people I've known who take the counter but keep the same job responsibilities and environment end up leaving before 6 months. I had a counter that was going to double my salary. "You thought I was worth that much but you decided to keep me in the dark?" Ya, I left and never regretted it.