SUMPRODUCT and SUBTOTAL for STRUCTURED TABLE

p45cal · #5 04-19-2024, 05:17 AM

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))

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
pivot table subtotal questions	sarana	Excel	6	01-27-2019 08:22 PM
Excel Structured Referencing: non-English reference?	tinfanide	Excel	0	09-05-2015 04:41 AM
Creating macros for a well structured analysis system	chillerlegend	Excel Programming	1	06-21-2015 05:28 AM
My subtotal and sum	Anmol786	Excel	1	01-18-2015 01:43 AM
Each subtotal signature require only at last page for each subtotal.	aligahk06	Excel	0	05-07-2010 11:07 PM