We have a "1st generation" Roomba. Our Roomba is not really loud, but it's not quiet either. At our house, I can hear it from a few rooms away, but not from upstairs. It's about as noisy as a hand vacuum cleaner like a Dirt Devil.
In terms of cleaning, does about as good a job as a hand vacuum. It will pick up items but doesn't really "scrub" the carpet the way that a large upright vacuum would do. We use it to clean up food under the table after our 3 year old finishes dinner and it does well enough with this task. It's about as good as if I got a little hand vacuum and did it myself. But we still need to get the upright out at least once a week and really vacuum it.
My mother has a Scooba and it is a bit better in my experience. It's a bit quieter and it does a better job cleaning tile floors than the Roomba does at cleaning carpets. The Scooba works well and you can actually put the mop away most of the time and let Scooba do it instead.
I've heard the newer Roomba do a better job vacuuming - that they are smarter and that it does a more thorough job in cleaning a carpet. So maybe things have improved over our Roomba experience.
For us, the Roomba is something between a toy and a useful appliance. It helps with cleaning up the kitchen carpet, but not a lot, but it's entertaining to watch and there's something neat about putting down a gizmo on a carpet and coming back and that carpet being mostly clean. Some sort of 21st century appeal that's unexplainable. I don't regret that we bought one, but it's not been as useful as I thought it would be.