AFAIK someone on reddit did the math and the break-even for tapes is between 50TB to 100TB. Any less and it's cheaper to get a bunch of hard drives.
Used drives from a few generations back work just fine, and are affordable. I have an LTO-5 drive, and new tapes are around $30 where I am. One tape holds 1,5TB uncompressed.
I think they are great for critical data. I have documents and photos on them.
The LTO consortium consists of HP, IBM and one other company I believe. Now, in my opinion, none of this guarantees the longevity of the medium any more than any other medium, but when I initially looked into it, this was enough to convince me to buy a drive and a couple of tapes.
My reasoning was that with the advertised longevity of 30 years under "ideal archival conditions", if I can get 10 years of mileage from tapes that are just sitting on my non-environmentally-controlled shelf, that means I'll only have to hunt down new tapes 3 times in my remaining lifetime, and after that it will be someone else's problem.
Not great for easy read access but other than that it might be decent storage.