The games I purchased (not downloaded, but purchased in a retail box or jewel case) which require Steam are DOOM 3 BFG, Titan Quest Gold (unfortunately it seems I got the Steam version), Worms Crazy Golf and Magika Plus. I was going to purchase the Stronghold Collection until I was lucky enough to spot the little Steam logo on the box.
It was not my intention for this thread to turn into either a pro or con discussion of Steam. The bottom line is buying a game which requires Steam should be left up to the purchaser of the software. I just believe publishers and developers should list Steam in the system requirements rather than as a little icon on the back of the box along with an added note in the fine print. Looking up a game on a publishers website will not help - you'll find most do not list Steam as a requirement even though you can't use the software without it (DOOM 3 BFG or Titan Quest for example). So if you buy games online and don't see the back of the box or can't read the fine print, you may get stuck. My main complaint is the Steam requirement not being made prominant on those games which require Steam. I have nothing against the Steam service itself for those who wish to participate.
If you like Steam - great. However, for myself I have no desire to take advantage of any features (like being able to save on one machine and pick up on another, or be mobile) or online play.
And there is the great unspoken truth. Should Steam go away, what happens to your software investment? Gamespy went away last year leaving some games in the lurch. It seems to me the loss of Steam would have a more significant impact.
Joe