I think it IS a problem in some cases, for example the overuse of it in online games (ha ha, although even then there are exceptions even here, few and far between in my opinion, but they do exist). However as has been pointed out there are games like The Walking Dead which, despite prolific use, feel natural to the dark, post-apocalyptic world lived in and thus feels acceptable.
That said, I do think it's acceptable in the other extreme. Bulletstorm's profanity also felt like an integral part of the game, for pretty much the opposite reason then The Walking Dead. Prototype was a bit of a grey area for me, almost going between those two extremes. No, it wasn't MEANT to be comedic, but the combat sometimes felt that way, with how overpowered you were. It did lead to a couple of sudden jolts as i was reminded the story was rather dark, which did make the profanity a little frustrating.
I find it hard to pinpoint the exact kind of games I find it inappropriate to be honest. The Witcher did bug me a bit with it on a few occasions, despite the fact it was enforcing what the game was trying to be, a very dark fantasy. Can't say why, but it did.
My favorite use of profanity was probably Resistance 2 actually, I recall profanity being used twice in the game, and both times were points i'm like "yep, I can go with that".