Back before agile was everywhere, having a PM on all but the smaller projects was considered essential. Now, only the largest projects have people assigned to just in a PM capacity.
I guess I have two parts to this question - What kinds of projects still require Project Managers? What can projects managers do to add value in an agile world?
I am sure you have heard the popular saying:
Processes do not fail, People do
I have always believe that project managers are needed when you have an environment were "processes" are failing too much. In agile you have many iterations, called sprints, that tackle a list of enhancements or defects.
The first place where I see PMs adding value are:
It is important to make sure you have the right process in place to record, track, prioritize the list of features that you are going to work on your next sprint.
Also, PMs help minimize the interruptions to developers, unless you have a Business analyst which they end up doing part of the PMs job or the other way around.
PMs can also add value by making sure that developer are not over-committing and under-delivering which is a very common issue in inexperienced developers. Tangent: Developers are architects at heart, they want to solve problems, don't expect them to say no very often.
Having said all that. It is important to remember that PMs are expensive. You need to make sure that your firm is able to support them. Over the long run, you can see a PM's value within the first 90 days.
Hope this helps you clarify the questions.
There is a role for PM's on agile, but PM's need to be willing to accept that not all projects may be run the way they used to run them. Agile is a metaphor for embracing change and project managers need to embrace it in their profession.
I am probably 75% employed as a PM - the other 25% I fill in as designer, architect, tester, chief bottle washer, whatever the project needs. I work exclusively on what people would consider small projects - as small as 2-3 to up to 10 technical staff. Most of our project are 'agile' and I feel that my role as a PM is more important and valued than ever. Just taking Geo's example above: sprints are a popular time boxing management technique popularized by Scrum, but may not be the best choice on every project. The PM should be able to work with the team and to explain to the customer whether project X will use "sprints" or some other time boxing technique, or maybe even feature boxing.
Most projects are not 'purely agile' or 'purely plan-oriented'. I have never worked on a project that slavishly adhered to any single process methodology. A good agile PM should know how to pull from among many techniques, models and methods to create a project team that runs smoothly and effectively in its organizations environment.
Agile does not mean "No Project Management", there are plenty of management going on in agile projects, it's just that agile project tend to put more emphasis on different project artifacts than in traditional plan-driven projects. There is still plenty of room for an agile PM to work with individuals and interactions as opposed to processes and tools, customer collaboration as opposed to contract negotiation, and responding to change as opposed to following a plan.
To answer this question I'd start with a distinction of a project management as a role, one of crucial ones even in one-man projects, and project manager as a title.
Now, you will basically always have a person, or a group of them, who are fulfilling project manager's role, however it is perfectly OK if neither of them has "project manager" in their signature.
Actually, contrary to others, I don't consider having a PM is crucial in agile projects. If we take Scrum, which is the most popular agile method we won't find project manager mentioned anywhere. It is because project management role in Scrum is spread over the whole team, with some kind of special position of Product Owner.
It means that Scrum teams, especially more experienced ones, can perfectly deal without someone who is called project manager.
Note: I use Scrum just as one of examples here -- I know Kanban teams which follows the same pattern as well.
Usually the reason to introduce project manager to agile teams is the way the customer wants to run a project. If the agile team is working with the client who prefers to have project managed the classic or formal or whatever you call it way you likely want to have project manager on your side -- someone who would be translating the way the team works to the way the client wants to see it.
If the agile team can work directly with the client using methods they want, e.g. product owner on client's side, demo after each iteration, client's active participation in prioritizing work for every iteration, etc., you probably won't find project manager in such team.
Another reason to introduce project manager to agile teams is when your organization wants to have projects run classic way for whatever reasons. Then again, you may want to have PM on the top of the team so you have someone connecting both worlds.
This is the most important job of PM in agile teams -- being a bridge builder, as some teams need those bridges to translate their own methods of work to something which is expected outside of the team. This is natural since agile methods tell us little about formal side of project management so they're falling short whenever it is needed.
UPDATE: Chris added in comment one thing which is worth stressing and hasn't been stated directly: PM in agile teams is less of command and control type of person as power to make many project-related decisions is delegated to the team. I wouldn't go that far to say that Scrum Master is agile version of PM, because is isn't, but still if you consider the team has more power someone has to have it less, and it's usually a PM.
Project Managers are the people who eliminate barriers. In those scrum meetings, the developers answer the question "What is impeding you from meeting your goals, and what concerns do you have?". It's the Project Manager's job to eliminate those barriers and address the impediments to success.
Project Managers are also jacks of all trades. They have programming skills, marketing and business experience, can talk to customers and stakeholders, and be persuasive when support from management, the team, or other departments is necessary.
In agile, Project Managers are important because the environment constantly changes, and new challenges continuously arise. The project manager is able to not only keep things together, but in the process also gains familiarity with all aspects of the project in order to make the best, educated decisions as to it's future direction.
"Project Management" is happening when a group of people is creating some product or service with a limited amount of time and resources. "Project Manager" is someone who is in charge of applying project management tools and techniques to the project management.
If there is no explicitly defined project manager role - than the tools and techniques are applied by someone else, or are not applied at all.
Thus, the answer is that every project requires a project manager.
There is no role of a Project Manager (defined by Prince2/PMI) in an Agile product delivery. This is because the traditional responsibilities of a Project Manager (termed Project Management) are split between the following:
I'm using the Scrum terminology as it has been adopted by more than 90% of those doing Agile. Note that the Agile Project Manager, also coined in Prince2 and PMI, is an oxymoron and should be avoided. It is a wolf dressed in sheep's clothing and is more likely to result in a negative impact to your product. (Refer to the Standish Group's Chaos Report which found an inverse relationship between PMI/Prince2 practices and value delivered to customers.)
To facilitate a transition from traditional software development to agile software development your organisation needs to move away from the concept of Project and towards the concept of Product. This is critical to building awesome products that meet your customers needs and is one of the hardest transitions for an organisation.
