The amount and quality of unit testing to be done in a project is directly proportional to the pace of development and the required stability of the product.

At one end, for a one-off script, trivial demo, or in a quick-paced-startup environment during the very early stages, it's unlikely unit tests will matter or be useful for anything whatsoever. They are almost always, almost certainly, a waste of time and resources.

At the other end, when writing software to control pace makers, or pilot rocket-ships, or when working at maintaining stable, highly complex, software systems (say, a compiler with heavy adoption across the multiverse) multiple layers of unit test, and other coverage, are crucial.

[There are loads of other situation specific factors which each contribute one way or the other, such as the size of your team, your budget, the talent of your developers, your preferred software dev strategy, etc... Generally, and at the highest level of abstraction, I find these two make the largest difference]