Loading css files conditionally useful?

There is no hard rule on this.

Loading JS is discussed often because JS is more likely to conflict when a lot of plugins throw their scripts into the mix.

CSS is less problematic in that regard. Still the same premise apply — you should aim for solution that performs well.

In a situation you describe, with highly contextual sites of large size, it certainly makes sense to separate them into dedicated stylesheet.

In other cases complicating development and logic for insignificant benefit might be impractical.