Actually, yes, and not in a blind, fanboy sense.
He's disliked some games I have enjoyed thoroughly - like Oblivion, for example, or Mass Effect 2 - but he is generally spot-on about things about the game that aren't right or outright suck.
For instance, he was absolutely correct about Saint's Row 2, a title I bought only because he wrote a fucking love sonnet to it. I would have ignored it otherwise, that game has become one of my absolute favorite titles.
He is going for comedy and that is great, but he is also a gamer and what he says about the games he plays cannot be discounted merely because he says them in an amusing way and is nearly impossible to please.
To be honest, I very much appreciate that he is nearly impossible to please. I may enjoy a game he dislikes, but when he rips a game to pieces, there is a strong chance I will fucking hate it also and this correlation has not escaped me.
And again, when he, who is impossible to please, seems really fucking pleased - Psychonauts, Batman: Arkham Asylum, Fallout 3, Saint's Row 2 - it is invariably a game that is actually genuinely fucking good.
Contrast this with practically every other reviewer in the world, who may, on occasion, shit themselves with glee over a game that is only just barely on-par, or reviewers with... how can i put this... clear motives.
Like a glowing review for an at-best-decent-at-worst-crapheap game that just so happens to be heavily advertised on the site.
I do not use Zero Punctuation as a purchasing bible, but if Yahtzee has reviewed a title I am considering buying, fuck yes i will carefully listen to what he has to say about it.
If the word "shit" appears more than five times in the review, I'm probably not going to be buying that game.