Based on the implementation at my workplace, SP seems to have value as a hybrid intranet site and file share. Each department has their own section in which non-IT folks can create simple HTML content and can attach documents. It is also relatively simple to share this content with other groups as needed, based on membership in Active Directory groups.

More controversially, SP also seems to be replacing MS Access as the non-IT-sanctioned application platform of choice. Instead of creating Access databases on a file share, people use SP "lists" to store data. The controversy stems from the fact that these lists often outgrow the practical limits of SP and need to be migrated to "real" systems.

Many people also seem to want to use SP as a Content Management System or even as the preferred application development platform (in which all application functionality is delivered as SP "web parts", which plug in to SP pages). These uses seem to cause as many problems as they solve, but that's just my opinion.