Unless its a catastrophic failure you usually won't be able to tell it's bad by looking at it. A radiator shop can pressure test the cooler for you. They will likely charge less than $20 for this. I brought mine to a local place and they did it for free.
The fact that you have water in the transmission suggests 2 possible scenarios:
1) you have a bad seal AND you rode around with a lot of water in the bilge. The water can't just get in there because the seal is bad. You'd have to have enough water in the hull up to the level of the seal at least under acceleration when the water runs to the back.
2) bad cooler. Water pushes into the transmission from the cooler leak, displacing the oil until it flows out of the vent.