How profitable are trading algorithms?

Are trading algorithms really as profitable as they're often made out to be? I've heard of traders making millions using them, but is that the norm or just the exception? Do they work in all market conditions, or are they more effective in specific scenarios? And how do you even go about developing a trading algorithm that's profitable? Is it a matter of trial and error, or is there a more systematic approach to it? I'm curious to know more about the ins and outs of this increasingly popular trading method.