A lot of these depend on the model and where it’s installed (geographically and within the house). In many areas of the US, there is a drain in the floor near all water heaters as a matter of code, you can drain condensate directly to that (and unlike gas appliances, the condensate is clean and does not need treated to go in household drains). I honestly think the noise concern is hugely overblown and used as an excuse for people that don’t like change. Sure it exists, but if your water heater is in the basement or garage like the majority are at least in my areas of the US, you’ll never notice it. I also look at the cooling air as a benefit for at least half the year, I can close all the vents in our basement for the whole spring/summer and it’s super comfortable. In the winter it’s a tad chilly, but not uncomfortable. Drying the air is also great for our basement, it’s literally a dehumidifier in what’s usually a pretty damp location for many people. Ours (a rheem unit) has a flimsy plastic air filter you vaccuum off once a year as well so not really a huge deal. I think most are like this.
I agree that folks should do their homework and understand what they are getting. Heat pump water heaters are great, but are overpriced at the moment. Even with electric company rebates and a tax credit it cost more than a decent gas replacement would for us. It’s likely to only pay off because we have solar and so don’t really pay for electricity for a large chunk of the year. But I expect costs to come down over the next 5-10 years as these become the go to for most electric installs (and with fewer new gas hookups in new and renovated buildings that’s like to be most installs). Once these hit that $700-$1000 price point there’s really no good reason most people shouldn’t default to installing one.