Forum Discussion
Summing value based on common ID between two different sheets
Hi all,
I am trying to sum a certain value based on certain criteria between different sheets.
In the below picture, I would like to sum the Total GLA based on the Asset Manager.
The Asset Managers are stored on a separate sheet as seen below.
The GLA is again stored on another separate sheet as seen below.
Normally, to complete such a function I would use the SUMIFS formula. However, due to location on different sheets, this does not work. Additionally, there is the added difficulty that the common denominator between the sheets is the property ID and not the asset manager, as the asset manager is not listed on the same sheet with the GLA. In PowerBI, I could use a relationship functionality but I am trying to stay away from Pivot Tables or Power Pivot. What would be the best way to accomplish this?
Much appreciation to any help!
1 Reply
In row 2 of the Total GLA (Sq. Fr.) column:
=SUMIF('GLA Sheet'!A:A, INDEX('Manager Sheet'!A:A, MATCH(A2, 'Manager Sheet'!E:E, 0)), 'GLA Sheet'!K:K)
where:
GLA Sheet is the name of the worksheet containing the GLA information; column A contains the ID and column K the GLA.
Manager Sheet is the name of the worksheet listing the Asset Managers; column A contains the ID and column E the name.
If you have Microsoft 365 or Office 2021, you can replace INDEX('Manager Sheet'!A:A, MATCH(A2, 'Manager Sheet'!E:E, 0)) with XLOOKUP(A2, 'Manager Sheet'!E:E, 'Manager Sheet'!A:A)
