So tell me, what's so special about it, I know it's made for Valve games, and that my little brother turns into a big fanboy when "G-mod" is the subject...It's also known as Gary's mod, or something..
Gary's mod is a random game where you can take any model from a valve game (or you can download them, this includes people, objects etc.) and play around with them. You can go across loads of valve maps from Team fortress 2 or other games (also there are downloads available) and just animate the people or make them do stupid things/put them in stupid poses. I don't have it, my sister does and I've watched her do some strange things on it before. It's main purpose is to just be silly I think, well, that's all I'm aware of.
It's basically a Mod playground. You can add characters, objects, weapons, alter physics, etc. It's used alot of the time to make game modes that other people can play by themselves or in a team. Look on Youtube if you want to see some of the ways it's been used.
Basically what Ienzo said, you can load models and maps from other games. You can also take weapons and such across, like you could have the portal gun in half-life and actually have it work and vice-versa. Its really cool