From my little window in the industry it's indispensable for FX and simulation (water, fire, destruction), but the tools for modelling, texturing, and animation just aren't worth the time. I don't know anyone who uses it for that or would reccomend it for those things.
I've gone through their basic tutorials. It's really nice to make procedural reusable things (a ladder that will create rungs based on the height), but I can't even imagine trying to model something organic.
I did commercials where animators where taught to use it, and that was a success in my opinion. They were happy because the animation tools are basically the same and the rigs were updated faster than they would be on other packages.
And it is true I've rarely seen models being done in Houdini. The only time I did one was for this commercial (https://vimeo.com/17808406), where the character is quite procedural. Had it been a more classical character, a Maya modeller would have done it.
It has a reputation of being difficult or long to learn, but I disagree. I find it way more intuitive, as you can see step by step what is happening. Their doc also has loads of examples and there are nice forums to ask for help (http://odforce.net and http://www.sidefx.com/forum).
If you have a logical mind, it should appear straightforward.