Dear all,

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

Hi and welcome
please post a sample sheet with some data and expected results ( no pics please); Thx

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Here you have the sample file.

Many thanks

Attached Files

EXAMPLE.xlsx (388.7 KB, 2 views)

Cross post SUMPRODUCT and SUBTOTAL combined in STRUCTURED TABLE | MrExcel Message Board
and
SUMPRODUCT and SUBTOTAL for STRUCTURED TABLE

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Try in D3:or a bit shorter:

This is perfect, it works. Thanks

Please mark thread " solved". Thanks ( see Thread Tools)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post