Can I create a COUNTIF calculated column in SharePoint?

Question

Is there a way to create a SharePoint calculated column that returns a count of the number of entries in a list? So If I have 3 customers in my list with the company "Starbucks" I'd like the field to return "3"

Answer 1

(Edited some wording for clarity per suggestion from dariom).

You may be able to get what you want with another list using a not-so-well-known variation of a lookup column.

Let's say you have a list called Companies with values in the title column like "Starbucks", "Peets", etc. Now you also have the Customers list you refer to, but the "Company" column is a lookup column pointing to the title column in the Companies list.

You can add a count very similar to what you described to your Companies list. Go to your Companies list, add a column of type "Lookup" referring to the Customers list and you'll notice that in the drop-down area where you define the lookup if you point back to the Customers list, you'll have a new option called "Count Related". This is here automatically because it recognizes that the Customers list has a lookup pointing back to this one. Select that Count Related option and now your Companies list will have a column counting how many customers are associated with that company.

No coding, Javascript hacks, or anything. Just hidden SharePoint auto-magic.

Answer 2

No, I don't think there's a way to do this using the out-of-the-box calculated column.

Some other ways you could accomplish this are:

• Create a view for your list that with a group by on the company field and include the total count. This is easiest, but might not be exactly what you're looking for.
• Create a custom column type that executes a CAML query to find items that you're interested in. There is a learning curve if you've not done it before and if the list that you're adding this custom column to has lots of rows, you'll be executing a query for each row which is inefficient - it'll be OK for a small number of rows.
• Use an event handler on the list that updates a column value each time a new item is added or removed from a list. This is easier, but can also be inefficient if you have a large number of items in your list.