There are absolutely valid use cases, but they are much fewer and further between than people claim.
Considering the amount of times my email has ended up in a leaked dataset, and the only accounts I've ever had visibly compromised were ones I did not use a password manager for, this seams to be the correct mindset.
I simply can't remember dozens of passwords, so a pw manager is the best I can do realistically. Yes, it's a single point of failure, but so is using the same pw everywhere.
Even if your password vault is stored on the cloud you're likely using a very secure passphrase for it that has 0 reuse anywhere else, so even if your password vault is stolen it's impossible to brute force.
For a hacker to comprise your password vault it would likely involve hacking your computer, which if you're keeping your software updated is a very difficult task these days without the target user's active help.
I think if used correctly they can be a net benefit, but the question is how many users actually use them correctly. Isn't the security they offer based on a user only having to remember a single complex and unique password for the manager, and then let it handle unique and complex passwords for everything else. The question is, however, how many users just set the password manager password to 'ImSecure123!' and use it to autofill the same old reused passwords they've always used?
It's not at all akin to that.
Firstly, every respectable password manager requires multi-factor authentication to log in to. Someone finding out the password to your manager is almost never sufficient. They would probably need to find it out as well as gain physical access to a device of yours which has the manager installed.
Secondly, the whole issue of "use one password for everything" is that if one site gets hacked and they store passwords insecurely (or, indeed, if the people who run the site are themselves malicious), then someone can use that same password to access all of your other accounts. So you have to trust the security of every single site you make an account with.
Using a password manager doesn't have that problem, since each site is being provided with a different password. So then you don't have to trust any website, you only have to trust the password manager itself. And you don't have to use a big cloud-hosted one if you distrust them - there are many password managers that you can just run locally on your computer (though without the cloud benefits of backup / disaster recovery). You can also just use a notebook with a padlock or something - frankly it doesn't really matter how you track your passwords, as long as nobody can get to it but you, and you use a different password for everything, and you have some plan for disaster recovery. That's the idea.