It doesn't do everything, but it's exciting for a number of reasons.
1) Javascript is a nice, powerful language. It's easy to find JS programmers. Bonus: you can share code between the server and browser.
2) Node provides a clean slate for server-side web dev: fresh, largely asynchronous libraries delivered via a well-designed package manager (npm) that I don't cry when I have to use.
3) It's an accessible way to program asynchronous server-side TCP/IP applications. Socket.io (WebSocket sever with multiple transports to fallback to) is an example of why this is useful.
The lack of interprocess communication that would help parallelize tasks between cores is the one thing that's lacking. AFAIK there are plans to address this (although given Erlang's maturity it will likely still be superior for this use-case) but even without that Node is very, very useful to many people right now.
You can just start up X instances of your server for X cores. This way you have completely independent processes running and maintain data independence without getting bogged down with synchronization and other messy issues.
Does anybody know if it's guaranteed to spread across your cores evenly (exactly one node proc on each core)? I skimmed the source but couldn't figure it out.
(won't belabor the point about node/v8's architecture and multi-cores/systems)