You’re right they’re by far the exception as to GA airplanes and are mostly unheard of in larger engines / aircraft. I’ll fill in that “mostly” later.
Partly it’s history. Multiengine airplanes date back to the very early days. As such, the early engines all turned the same way and torque, etc., is just what it was.
Once somebody decided to invent counter-rotating engines, the usual way for ICEs is not with a reversing transmission. Instead you grind the camshaft as a mirror image of the normal, rewire the starter motor to turn the other way and viola! The engine runs the the other way. There’s a few more details, like oil pump impellers & distributor timing, but the basics are only a couple of key parts need to be swapped out and there’s no extra weight. Of course the prop also needs a bunch of mirror imaging as well.
Big Picture, the benefit of counterrotating engines is two-fold: with both engines running there’s no net torque and P-factor. And with one failed there’s not a more critical and less critical engine.
The latter was the big motivation for the few light twins that have counter-rotating engines. The Vmc we’ve talked so much about is computed for the worst of the two engines. Changing the worst engine to rotate the other way can save ~5mph of Vmc which in turn leads to better book take off and landing distances, speeds, rates of climb, etc.
At the time in the late 60s when GA was booming, light twins were developing a real bad rep from takeoff Vmc accidents. With the Twin Comanche being the wosrt offender. It’s not a coincidence that that model was one of the first c/r light twins as Piper tried everything to clean up that reputation.
Cessna took a different tack with the 336/337 Skymaster to eliminate Vmc problems seen in the 310/320. That also didn’t work very well; the Vmc problem was comprehensively solved, but at a terrible cost to performance = speed, climb rate, payload, and, yes, single engine performance once you got everything squared away.
The WWII Lockheed P-38 was one of the first counter-rotating engine airplanes. They did that mostly for balanced torque/P-factor and maneuverability as a fighter, not for Vmc. Funny enough, the early P-38’s engines counterrotated in the way that both engines were the worst case Vmc in the event of an engine failure. In the next iteration they changed which engine turned which way so both were best-case for Vmc.
Interestingly the original Wright Flyer from 1903 had a single engine but had contrarotating props. The Wright Brothers saw the torque issue from Day One. If you look closely at any picture taken from the rear you’ll see two long bicycle chains running to the props. One has a half-twist along the way and one does not. That’s the “reversing gear” right there in plain sight.
Jumping to modern times …
Turboprops all have reduction gearboxes; turbines turn vary fast & props turn very slowly, so there’s no esacaping the need for a gearbox. For a counter-rotating model there will be an extra idler gear in there to reverse the prop rotation. Building a complete gas turbine to turn the other way would be a lot of extra cost and massive numbers of differently-handed parts; far more than just a camshaft and couple accesory tidbits.
The Airbus A400M is interesting because they put counter-rotating props on each wing. The inboards are mirror-imaged and the outboards are also mirror-imaged, but in the opposite direction. So each wing has one of each. The cited wiki has a paragraph or two on the rationale for this. AFAIK this is the only such airplane ever built.