Others

Can You Use SUMIFS in SharePoint Calculated Columns?

Maira Mehtab Sep 22, 2026 869 views

Question details

The user wants to know if it is possible to use the SUMIFS function within SharePoint calculated columns to conditionally aggregate data across multiple list items, similar to Excel.

Product: SharePoint
Device & OS: not provided
Scenario: Attempting to calculate dynamic totals conditionally across multiple items in a SharePoint list.
Observed behavior: SharePoint calculated columns do not natively support Excel-style SUMIFS functionality, as they cannot aggregate values across multiple list items or rows.

Before you start

Evaluate your current SharePoint list structure and verify if you have access to external tools like Microsoft Power Automate or Power BI before attempting a redesign.

Solution 1Recommended

Restructure List and Use Power Automate or Power BI

Since SharePoint calculated columns lack SUMIFS support, you must redesign your list format and use external workflow or reporting tools for conditional aggregation.

SharePoint calculated columns are strictly designed to perform row-level calculations. They cannot scan or aggregate data across multiple items (rows) in the same way Excel does with SUMIFS. To achieve cross-item totals, you need to rely on specialized reporting or workflow solutions.

Redesign your list structure

Modify your SharePoint list data entry process so that each month or data point is stored as a separate, individual item rather than spreading totals across multiple columns.

Connect Power Automate

Set up a Power Automate flow triggered by item creation or modification. Configure the flow to query the list, calculate totals through the current date, and write the aggregated data into a separate tracking list.

Utilize Power BI for reporting

Connect Power BI directly to your SharePoint list. Once connected, use DAX formulas (such as CALCULATE and SUM) within Power BI to easily replicate the conditional SUMIFS behavior dynamically.

Community Support: If you are unfamiliar with building workflows, the official Power Automate community forums provide excellent guidance and templates for iterating through SharePoint lists.

Free Microsoft Office alternative

Perform Complex Data Aggregations Easily with WPS Spreadsheet

While SharePoint lists struggle with advanced cross-item calculations like SUMIFS, WPS Spreadsheet handles complex conditional aggregations effortlessly. WPS Office is a free, lightweight suite highly compatible with Microsoft Excel formats, providing a familiar interface to analyze your exported data natively without complex database setups.

1. Export your SharePoint list: Navigate to your SharePoint list and click 'Export to Excel' to download your data as an .iqy or .csv file.
2. Open with WPS Spreadsheet: Launch WPS Office and open the exported data file directly within the WPS Spreadsheet application.
3. Apply the SUMIFS function: Select an empty cell, type '=SUMIFS(', and select your ranges and criteria to instantly calculate your conditional aggregations without relying on complex workflows.

Fully supports SUMIFS, COUNTIFS, and other advanced array formulas natively.Seamless compatibility with Microsoft Excel (.xlsx, .xls, .csv) formats.Lightweight installation requiring zero complex database or server setups.Familiar user interface ensuring zero learning curve for users switching from Excel.

microsoft office alternative - wps office

Frequently Asked Questions

Why does SharePoint not support the SUMIFS function?

SharePoint calculated columns only compute data within the exact same row (list item) based on other columns in that specific row. Functions like SUMIFS require scanning and evaluating multiple rows, which is not supported by SharePoint's basic calculation engine.

Can I use a basic SUM function in a SharePoint calculated column?

Yes, you can use the basic SUM function in a calculated column, but only to add up values from different columns within the exact same list item. You cannot use it to sum a single column's values across multiple different items.

Is there a workaround to see totals in SharePoint without using Power Automate?

Yes, you can use the 'Group By' and 'Totals' features in a SharePoint View. This will visually display aggregated sums for specific categories on the list page, but these totals are strictly visual and cannot be stored in a column or used for further calculations.

Office Quick Solutions