Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can I create a COUNTIF calculated column in SharePoint?

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"

like image 918
Whozumommy Avatar asked Jun 10 '09 20:06

Whozumommy


People also ask

How do I create a calculated value list in SharePoint?

To add a calculated column, click + add column then select More. Note: Calculated fields can only operate on their own row, so you can't reference a value in another row, or columns contained in another list or library.

Do Excel formulas work in SharePoint?

Excel will probably always be the ultimate program for creating formulas but SharePoint can also use formulas as well. When working in a SharePoint list it is easy for someone who has knowledge of using formulas in Excel to start creating calculated columns in SharePoint.

Can SharePoint lists do calculations?

You can use formulas and functions in SharePoint lists or libraries to calculate data in a variety of ways.

How do you use calculated field value in SharePoint?

Open the SharePoint list or document library, then click on + Add column -> More… Then it will open the Create Column dialog box, provide a name for the Calculated column and then choose the Type as Calculated (Calculation based on other columns) options.


2 Answers

(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. Column Related

like image 137
Sam Yates Avatar answered Oct 22 '22 02:10

Sam Yates


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.
like image 38
dariom Avatar answered Oct 22 '22 00:10

dariom