SharePoint calculated columns

Question

Can I create a calculated column that totals the record count in another list based on a field value?

Example: List1 contains departments, each with a deptID List2 contains employees, each assigned to a deptID

Can List1 contain a total employee count (for that deptID)?

Answer 1

No, this field does not exist. You can create custom view or workflow (or maybe eventreciever) on add item in the list2, which will change corresponding field of conformity item in the list1.

But it's best just to create the custom view.

Answer 2

No, calculated field can refer only to fields in the same item.

I suggest you to use EventReceiver to accomplish your request. Register the receiver to the employee list with ItemAdded, ItemUpdated and ItemDeleting events. In the receiver's methods update corresponding department item and its field (number of employees).