Calculations in list

Question

I am attempting to move away from excel and move to lists.

We currently have a spreadsheet with numerous rows relating to individual assets. Each of these assets are part of a group. Each asset has some numerical attributes I would like to calculate values on based on the group they are part of.

This is quite easy in excel, using the sumif formula but I cannot figure out how to do this in lists.

I currently have a list set up with the following fields:

AssetNumber (unique)

Group (text)

Length (number)

Cost (number)

I would like to be able to get a total length and a total cost for all assets in each individual group but cannot fathom out how to do it. Can anyone help?

Answer 1

Dear @Iain Cameron,

I hope you’re having a good day.

In Microsoft Lists, calculations across multiple items are done in Views, not in columns. Calculated columns work only per item, but grouped views with totals are designed specifically for summaries like this.

Here’s the simplest and supported way to see total Length and total Cost per Group.

First, open your list and create a new view (or edit the current one). Views only change how data is displayed, not the data itself.

Once you’re editing the view, find the Group by option and select the Group column. After you save this, your assets will be grouped so that all items in the same Group appear together.

Next, while still in the view settings, scroll down to the Totals section. For the Length column, set the total to Sum. Do the same for the Cost column, and then save the view.

When you go back to the list, you’ll now see each Group displayed separately, with the sum of Length and Cost shown for that group. This gives you the same result you’d normally achieve with SUMIF or a PivotTable in Excel, just presented differently.

If this helps solve what you were trying to do, feel free to mark the answer as Accepted, it helps others who are moving from Excel to Lists find the right approach more quickly.

If you run into any issues while trying the steps, or if something still doesn’t feel quite right, please don’t hesitate to reach out again. I’ll do my best to support you however I can.  

Looking forward to hearing back from you with any updates or additional details. 

Warm regards, 

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.   

Answer 2

SharePoint list calculated columns cannot aggregate across multiple rows, so a SUMIF-style “total per group” cannot be done directly in a calculated column.

Key limitations:

• 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.”
• This prevents formulas like “sum Length for all items where Group = this item’s Group.”

Available options within Lists/SharePoint:

1. Use list views with grouping

• In the list, create or edit a view.
• Group by the Group column.
• In the view settings, enable totals (e.g., set Length and Cost to show Sum).
• The view will then display a subtotal of Length and Cost for each Group section, but it will not store those totals in columns.

2. Use external calculation (Excel or Power BI)

• Export the list to Excel and use SUMIF/SUMIFS there, or
• Connect the list to Power BI and create measures that sum Length and Cost by Group.

3. Use automation/custom code (not in the provided context, but conceptually)

• A separate process (e.g., Power Automate or custom code) can read all items, compute totals per Group, and write them to another list or to a “summary” item per group.

If a calculated column is still needed for row-level math (for example, Cost per unit), it can be created as follows:

1. Open the list, go to Settings → List settings → Create Column.
2. Choose Calculated (calculation based on other columns).
3. Enter a formula that uses only columns from the same row, such as =[Length]*[Cost].
4. Choose the appropriate return data type and save.

But cross-row aggregation like SUMIF by Group must be done via views, external tools, or automation, not a calculated column.

---

References:

• Examples of common formulas in lists
• Calculate data in lists or libraries