Sound waves can be looked at from a 'time domain' or 'frequency domain' point of view. Alphonso's answer is a good one from a 'time domain' viewpoint; I'll answer from a 'frequency domain' angle.
As I mention in this answer, the human ear analyses sound in the frequency domain - it is constantly monitoring how much energy there is at each frequency. However, the auditory system also tries to simplify things for us by working out which points of energy excitation are at multiples of the same frequency, and are thereby likely to be components of the same sound.
This mechanism isn't only triggered by sounds that are integer multiples - it can also kick in when we have other simple rations. For example, if we have a sine wave at 500 Hz, and another at 750 Hz, they're both multiples of 250 Hz - this doesn't necessarily mean we'll hear the combination as a 250Hz tone, but they will sound consonant together. To relate this to Alphonso's answer, every third peak of the 750Hz tone (or possibly a different point on the wave, depending on phase) would line up with every second peak of the 500Hz one.
If two sine waves can have consonance due to simple ratios how does the overtone/harmonic series fit in the picture?
Consonance / dissonance is a result of the full set of partials the ear can hear. You don't need to draw a logical line between the fundamental and the overtones; they're all in the mix together.
If each sound has more partials, there are more frequency points for the ear to try to 'make sense of' and find relationships between. This can have different effects on the level of consonance - for example, if you play a nice perfect fifth (or as near as you can get, given the piano) somewhere in the middle of the piano, there are more groups of partials with simple ratio relationships, and you'll get a lovely rich sound. On the other hand, if you play a fifth down at the bottom of the piano, you'll get a more dissonant sound, as the low harmonics are weaker, and the higher harmonics ae more stretched due to string stiffness.