Yes, you will still have to sacrifice the creature even if the spell that triggered it gets countered.
Let's use Frost Walker as an example. The following events happen:
- Your opponent casts Lightning Bolt, targeting Frost Walker.
- Frost Walker's ability triggers.
- You cast Counterspell, targeting Lightning Bolt.
- Counterspell resolves. Lightning Bolt is countered.
- Frost Walker's ability resolves. You sacrifice Frost Walker.
Countering Lightning Bolt does not also counter Frost Walker's triggered ability. You could, however, use Stifle to counter the ability itself.
Spellskite cannot save Frost Walker. Frost Walker triggers immediately upon being targeted. Frost Walker's ability does not care if you redirect the target to Spellskite.
Standard Bearer can save Frost Walker, at least from spells that only have one target. If your opponent targets Frost Walker, you say "That's illegal. You have to target Standard Bearer." The game then rewinds to the point before your opponent cast the spell.
You still sacrifice the creature, even if the original spell is countered.
If a creature has the ability:
When [this creature] becomes the target of a spell or ability, sacrifice it.
and a spell or ability targets it, the following happens in your scenario:
- The spell or ability goes on the stack, targeting the creature.
- The creature's ability triggers, and goes on the stack.
- The counter spell goes on the stack, targeting the original spell or ability.
- The counter spell resolves, and the original spell or ability leaves the stack.
- The creature's ability resolves, and you sacrifice it.
Once the triggered ability is on the stack, removing the spell or ability that originally triggered it doesn't do anything to the triggered ability.