This changes everything. It really does.
I will be able to play co-op with my brother with me on PS3 and him on PC, or even another friend on a Mac. That in itself is mindblowing. The technology to do this has always existed, but Valve and Sony have finally cut down the red tape.
With Steam Cloud, I'll be able to play some of the single player campaign on PS3, then log in with my Steam account on a friend's PC and continue the same game, because my saved game is in the cloud. (edit: Haven't confirmed this 100% - sites say that saves are cloud-based, but cross-platform hasn't been specifically mentioned)
While it is a clever way for Valve to get people connected to Steam, they do it in a way that adds value. And this is huge value for gamers. If you buy the PS3 version, you can play the same game on PC as well, as well as being able to play multiplayer with anyone on a PC, Mac, or PS3.
Yesterday, I was wondering which version of Portal 2 I was going to buy.
Today, I know for sure.