Unit tests cause pain. Unit tests, by themselves, do not solve problems, do not make users happy, do not make maintainers happy, do not make a better product, and do not improve design. Unit tests make certain things hurt; nothing more and nothing less. But pain can indicate something is wrong and can lead you to do something else.
The first assumption behind unit tests (and bodily pain) is that if something hurts we'll pay attention and do something else instead. The second assumption is that what we do as an alternative will not only hurt less but make things better. For example, since unit tests make singletons and hard coded dependancies so painful, we can rely on dependency injection techniques and the single responsibility principal. This should cause code that is easier to test and understand and that responds to rapidly changing requirements.
So unit tests are one way to show what hurts and needs attention and avoid blundering into bad and potentially project-killing designs. The problem is when we can't or don't change how things are done. If we must rely on singletons, the filesystem, database, complex api calls, undefined behavior, or slow techniques then writing and running the unit tests will hurt. In these cases the costs of writing and maintaing unit tests can outweigh the benefits of running those tests.