I've lived in four geographically distinct areas (22 ping, 40 ping, 5 ping, 80 ping) in my time in competitive tf2 and played demo the whole time. Each jump in ping was noticeable, but 40 did not make me much worse than when I was at 22. 80 has had a pretty big impact though. For demoman stickies, ping is doubled in a sense since you have to wait for ping to shoot the sticky then wait again to be able to det it.
Close range is where ping difference is really noticeable. At close range with 5 ping you can treat pipes and rockets almost like hitscan. You just point where the dude is and click. You can even track at close range. At 80 ping you have to do a lot more prediction at close range because people will often switch direction within those 80 ms between when you click and the projectile actually comes out.
Some popular traps actually become close to impossible or more dangerous to watch on 80 ping vs like 40 and below.
Also shutter traps become way less likely to work when you have more than like 40 ping, cus people can peek the shutter and back away before you get a chance to det (although smart people shouldn't be peeking that close anyway)
Overall I'd say anything over 40 makes the game a significantly worse experience for the projectile player. Every single shot has to be calculated way earlier and is significantly less likely to hit. Your mistakes become magnified cus you can't dm your way out as easily or react as quickly. And that's coming from actual experience.
Oh I also remember playing league of legends ~4 years ago when all their servers moved from LA to Chicago (to improve overall player connection experience), and the ping drop was similar. I was living on the east coast at the time and noticed a big difference in the quality of gameplay. If ping makes that much of a difference in a MOBA game where reaction time isn't as crucial it definitely has a big impact on an FPS like TF2.