I'm attracted by the low interest, but scared of having too many credit cards and losing track of payments, or worse, adversely-affecting my credit rating or future borrowing potential by having "too much credit".
But maybe it's worth it for the cash savings?
First off, I recommend that you determine your current Credit Card balance, and how long you expect it will take to pay it off. Figure out how much of your payment will be going towards the CC's interest, and how much interest you will be paying in total. You need this info to make an informed decision.
Now you can look at the Credit Card offers and compare rates. Figure out how much you will save by lowering your interest rate. Remember that with a lower rate you can pay it off faster while making the same monthly payment. However, there are some things to watch out for in the fine print of the offer:
Balance transfer fees for the new card. You'll usually get charged a fee of $50-100, or a percentage of the balance. Make sure this fee doesn't exceed the interest rate savings on your new card!
Is the rate for balance transfers temporary? Will the interest rate on your balance go up after a few months? If so, will you have it paid off by then? Or will you be right back where you started?
If transferring the balance is going to save you a reasonable amount of money (enough to make the hassle worth it), then you should probably do it, assuming you are financially literate enough to understand how it all works. (Don't make a financial move you don't understand.)
As for your concerns:
Having too many cards and losing track. There is a legitimate concern there, but look at it this way: If your old card isn't competitive (which it probably isn't if you're transferring the balance elsewhere), then it's probably time to close it. You don't have to keep both cards around. And if the old one is paid off, it can sit with zero balance for a while (no payments to be made).
Note that a new card is not a license to spend more! You are getting the new card to reduce your total debt as efficiently as possible (by paying less interest). If you are worried that you will start buying things with both cards, then maybe this isn't a good idea.
Having "too much credit" available. This is not a problem for most people. Is your FICO above 800? If not, don't worry about it. What you should worry about is:
Credit Utilization - What is the the total limit of all your credit cards? What is the total balance of all your credit cards? Divide these numbers to get your utilization percentage. If this gets too high (you are maxed out), it will hurt your score.
Debt to Income Ratio - How much do you owe (Credit Card, Car Loans, Mortgage) versus how much you make? If this number gets too high, it looks like you are in over your head.
So, what will happen if you apply for a new card and transfer your balance? Will your total debt change? No. So your DTI ratio will stay the same. Will your Credit Utilization change? That depends on the credit limits of the old card vs new one, and whether or not you keep the old one open.
If the rate that it will end up at does not exceed what you currently have then trading up in credit cards can be a good idea anyways. However, once you have transferred the funds I would recommend canceling the old card. Surfing card balances can be an excellent technique for helping eliminate debt, but as you said, caution is imperative as having too many lines of credit open hurts your credit score.
I've always been leery of balance-transfer cards and introductory rates on such cards. Interest rates matter if you're in the habit of carrying balances. Credit cards are unsecured debt, which makes them an expensive way to borrow money.
You'd be better off paying off your current credit cards in full than transferring balances to new cards.
One other thing to be aware of is that a lot of these cards only apply the low introductory rate to the balance transferred to the card. Any new charges on such cards is likely to accumulate interest at a significantly higher rate.
