How to Calculate Values Across Two SharePoint Lists
Question details
Users need to perform live calculations, such as subtracting quantities, using data spanning across two different SharePoint lists.

- Product
- Microsoft SharePoint
- Device & OS
- not provided
- Scenario
- Tracking inventory or dynamically updating calculated results that require data inputs from multiple linked SharePoint lists.
- Observed behavior
- SharePoint prevents calculated columns from referencing values from lookup columns, making direct live calculations across separate lists impossible through native list settings.
Ensure you have adequate permissions to create and manage Power Automate flows in your Microsoft 365 environment, and that both SharePoint lists share a unique identifier (such as an item ID or part number) for accurate record matching.
Use Power Automate to Calculate Cross-List Values
Since SharePoint calculated columns cannot reference lookup columns, Power Automate provides an automated workflow to fetch, calculate, and update values between your lists.
SharePoint natively prevents a calculated column from directly using values from a lookup column across different lists. To bypass this limitation, you can build a flow in Power Automate that triggers whenever a list item changes. This flow will pull the necessary data from the secondary list, perform the required math, and update your target field automatically.
Open Microsoft Power Automate and create an 'Automated cloud flow'. Set the trigger to 'When an item is created or modified' and select your primary SharePoint list (e.g., List B).
Add the 'Get item' action for SharePoint. Point it to your secondary list (e.g., List A) and use the unique identifier (like part number or ID) from the trigger to fetch the correct record.
Initialize a variable or use a 'Compose' action to write an expression that performs your desired calculation. For example, subtract the quantity used from the available quantity using a formula like sub(outputs('Get_item')?['body/AvailableQuantity'], triggerOutputs()?['body/QuantityUsed']).
Add the 'Update item' action for SharePoint. Target the appropriate list and update the target quantity column with the output from your calculation step.

Manage Complex Datasets Offline with WPS Office
While SharePoint and Power Automate handle cloud-based list automations, working with complex datasets, VLOOKUPs, and cross-sheet calculations is often easier in a dedicated spreadsheet application. WPS Office provides a fully compatible, lightweight, and free alternative to Microsoft Excel for handling your heavy data calculations natively.
- 1. Install WPS Office: Download and install the free WPS Office suite to your device from the official website.
- 2. Open Your Spreadsheet Data: Export your SharePoint list data to a spreadsheet format and open it directly in WPS Spreadsheet.
- 3. Perform Cross-Sheet Calculations: Use built-in functions like VLOOKUP to easily reference and calculate values across different datasets without setting up online flows.

Frequently Asked Questions
Why can't I use a lookup column in a SharePoint calculated column?
SharePoint restricts calculated columns to only reference data from within the same item or row. Lookup columns retrieve data from separate lists, which SharePoint's native calculated fields do not support for live, automated updates.
Can I export SharePoint lists to a spreadsheet to perform these calculations?
Yes, you can export both SharePoint lists to an Excel spreadsheet and use formulas like VLOOKUP, XLOOKUP, or INDEX/MATCH to match IDs and calculate values across different tables much more easily.
Does Power Automate update the SharePoint list instantly?
Power Automate flows trigger based on their configuration and server load. An 'automated' trigger usually runs within seconds or a few minutes after an item is created or modified, so the calculation update is near real-time but not perfectly instantaneous.




