There can totally be value in it.... if you'll use it, and if it would be something that you would be willing to spend money for anyway.
For a specific example: there are periodically CC offers for Southwest Chase cards that award 40,000 - 50,000 miles when you open a new account and spend $2000 in the first few months. This card has a yearly fee of $80 (or something like that). I signed up for it knowing that I will use those points to fly to see my sister who lives several states away. I would pay the money for plane tickets anyway, but by using the benefits of the CC signup I will pay $80 for tickets that cost a lot more, and still have some points over for other travel. I will probably cancel the card after 10 months or so to avoid paying another annual fee.
It's definitely not free, because I'm paying the annual fee, and I'm putting $2000 of spending on the card (to get the bonus points) instead of using a card that gives me cash back or some other benefits. But I've run the numbers and found that this is worth it for me.
The big miles and points bloggers accumulate huge amounts of points and miles with every airline, hotel, and loyalty program possible - and they use their knowledge to score great trips. But it's a lot of work to keep up with, you have to spend a lot of money to meet minimum spend requirements, and if you don't have a plan or a lot of knowledge, a lot of the points will go to waste.
If you're new to the miles and points games, it's best to pick some specific vacation or trip and then figure out the best CC signup bonuses/programs for that. Like you might decide you want two plane tickets to France for next summer. So you do some research and figure out that based on your location and the various airline programs that United is your best bet, and you need 100,000 United miles to get the tickets. Then you figure out that you can sign up for the United Mileage Explorer card for a 50,000 mile bonus, and the Chase Ink Bold for 50,000 ultimate reward points (which transfer to United at 1:1). And finally, you have your points - yay! Next you have to find flight availability to your destination (which varies by time of year, location etc). But eventually - you get "free" travel!
The tl;dr version is: you can get a lot of benefits, but it's a lot of work and takes a fair amount of research. So start small! Good luck! :)
Last note - elite status (in any program) is expensive and hard to get unless you travel *a lot*. Generally not worth it.