The rule fails pretty quickly. For example, it almost systematically fails for anything in the third period and higher or for most oxygen species. However, it is indeed valuable for carbon and nitrogen centres.
If you would count double bonds twice and triple bonds three times, you would always, as a rule (carbenium ions not considered) arrive at $\mathrm{sp^3}$ for carbon. However, the idea of teaching students hybridisation is not only to explain how bonding can actually occur in carbon (which would have a $[\ce{Ne}]\,\mathrm{2s^2\,2p^2}$ in ground state) but also how double and triple bonds differ from single bonds.
In general, double and triple bonds are considered to derive from an underlying σ bond to which a number of π bonds (one or two) have been added. These π bonds are fundamentally different from σ bonds; the bonding orbitals of the latter are always directed towards the bond partner while the principal direction of the formers’ bonding orbitals is perpendicular to the bond axis. This perpendicular arrangement is achieved by forming the π bonds using unhybridised p orbitals, while σ bonds are formed with $\mathrm{sp}^n$ hybrid orbitals.
Therefore, you are actually counting the underlying σ bonds (and lone pairs, where applicable) rather than indiscriminately counting bonds. If you do that, you are ensured to always have sufficient p orbitals remaining unhybridised to form the necessary multiple bonds.
