Thread: [Solved] SUMPRODUCT and SUBTOTAL for STRUCTURED TABLE
View Single Post
 
Old 04-19-2024, 05:17 AM
p45cal's Avatar
p45cal p45cal is offline Windows 10 Office 2021
Expert
  
Join Date: Apr 2014
Posts: 947
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
Default
Try in D3:
Code:
=ROUND(SUMPRODUCT(SUBTOTAL(109,OFFSET(BALANCE[ANNUAL USAGE DECEMBER 2023],ROW(BALANCE[ANNUAL USAGE DECEMBER 2023])-MIN(ROW(BALANCE[ANNUAL USAGE DECEMBER 2023])),0,1)),BALANCE[BASE CASE (BUY FROM NASS) - DIRECT COST '[€']]),0)
or a bit shorter:
Code:
=LET(a,BALANCE[ANNUAL USAGE DECEMBER 2023],ROUND(SUMPRODUCT(SUBTOTAL(109,OFFSET(a,ROW(a)-MIN(ROW(a)),0,1)),BALANCE[BASE CASE (BUY FROM NASS) - DIRECT COST '[€']]),0))
Reply With Quote