Android in general lets you do this. Just go into the settings of whatever app you're trying to set the different notifications for, and you'll find a notifications setting in there somewhere.
Gmail lets you do different ringers for different labels. You can set a different tone for text messages, and have a default notification tone for everything else.
Just explore the options menus in everything and you'll find what you're looking for, I'm sure.