Idk about the additive effects, but:

Fan noise especially is very “white” in the sense that it contains a lot of frequencies from low to high. That makes it ideal for overpowering (and subjectively swallowing) other noises (like other fans, speech, etc).

Fan noise
Image

Speech for comparison happens in a frequency band where fans are very strong
Image

Running water is similar. When you try to listen to a conversation or a video with speech while washing your hands, you will have a hard time understanding anything. With other noises that are in different frequency bands or only use very small range of frequencies it wont have that effect even if the noise is much louder than the running water was.

Noise cancelling in the sense that one noise actually makes another noise disappear, doesnt really happen under non lab conditions. Its more of a drowning out effect where your ear/brain cant differentiate the two noises anymore.

Selective hearing can do a lot of work by processing the signals from your ear in your brain, but that isnt all powerful.