Cost is an issue. With counter-rotation you either need another gearbox or an engine set up to run the other way. Plus there is the extra expense of producing a propeller that is 'handed'. So the spares inventory goes up big-time.
For the average light twin putting out maybe 300 HP at the hub it is hardly worth the effort for a very small gain in handling and performance. Hence it never caught on. It was a sales gimmick.
Big military aircraft probably did need it, but for them cost was no object.