As a player, I've only used counterspelling once, but it was a very cool experience. My character was at 0 h.p., and about to get hit by a magic missle barrage that most likely would have been the end of him. So he counterspelled, which stopped the missles, but since spellcasting is a strenuous activity knocked him down to -1 anyway.
As a DM, I've yet to use it, but one of the challenges my PCs are going to face in their next adventure is a master counterspeller.
Cullain