If a spell is on an actual card (e.g. creature, enchantment, instant, etc., usually played from your opponent's hand), then countering that spell means the spell never happens, and the card is moved to its owner's graveyard. (If the spell is not on a card, for instance it's a copy of another spell, then it just disappears.)
Yes, if there are two spells on the stack, Double Negative will allow you to counter both of them, which in a best case scenario means two of your opponents cards are moved to the graveyard, having no effect other than the mana they cost to cast. Note, however, that this will almost certainly require that at least one of them is an instant, as very few non-instant cards may be played while another spell is on the stack (those that do carry the keyword "Flash").
Also note that if your opponent (and you) lets one spell resolve before playing the second spell, then there never were two spells on the stack simultaneously for Double Negative to counter. So if your opponent played a creature, you both let that creature come into play, and he then plays, say, a Shock to deal to damage to you, you can't counter both the Shock and the creature. You lost the opportunity to counter the creature before the Shock was ever a spell.