SUMPRODUCT and SUBTOTAL for STRUCTURED TABLE

Dear all,

Thanks to have accepted me in this forum.

I am struggling to find a solution to combine SUMPRODUCT and SUBTOTAL for a structured table.

I have a table with slicer, and I would like to get the SUMPRODUCT based on slicer selection.

By looking for a solution, I got this formula example:

=SUMPRODUCT(
SUBTOTAL(3,OFFSET('Raw Data'!F2:F150,ROW('Raw Data'!F2:F150)-ROW('Raw Data'!F2:F150),0)),
('Raw Data'!F2:F150=5)+0)


and I tried to apply this in my case. So, step by step:

- ROW(BALANCE[TOTAL SAVINGS '[€'] MARCH 2024])-MIN(ROW(BALANCE[TOTAL SAVINGS '[€'] MARCH 2024])) ==> This Works
- The next step is to apply the OFFSET function ==> now I am blocked

Could you help me?

Many thanks in advance

Add a helper column with

Formula:

Please Login or Register  to view this content.

and then use

Formula:

Please Login or Register  to view this content.

Many Many Many thanks

And what about using the function I listed on the first post?

I tend to avoid the use of volatile where reasonably possible.

Try, =SUMPRODUCT((BALANCE[BASE CASE (BUY FROM NASS) - DIRECT COST '[€']]*(BALANCE[ANNUAL USAGE DECEMBER 2023])*SUBTOTAL(3,OFFSET(BALANCE[[#Headers],[Product Type Description]],ROW(BALANCE[Product Type Description])-ROW(BALANCE[[#Headers],[Product Type Description]]),0,1,1))))