I'm getting confused trying to figure out the water pumps. I'm seeing massive fluctuations in the overall readout, can be in the positives one minute then severe negatives the next...and back to positive again. Other times I can double the number of pumps and the overall readout stays at 0. I'm just not understanding how it's working...

I haven't been playing for long so I may just be missing something.

To avoid filling up your storage with water the pumps turn off when it reaches a per colonist amount stored (you can set this to either a low or a high amount on the pump UI). The fluctuations means it's turning on and off, just keep a watch on your per-colonist amount stored and if it drops too low (you'll get warnings) place more pumps.

At the time it was happening it had "marginal" next to the per colonist amount.

Sometimes the problem are the factories that creates antibiotics or other products with water