SUMPRODUCT and SUBTOTAL combined in STRUCTURED TABLE

[EmmeEnne1979]

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

 

[shift-del]

Hi

Any reason why you wouldn't use a pivot table?

 

[EmmeEnne1979]

Hi

Any reason why you wouldn't use a pivot table?

I won't have any advantage to use a Pivot Table as my data are already grouped.

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

SUMPRODUCT and SUBTOTAL for STRUCTURED TABLE

SUMPRODUCT and SUBTOTAL for STRUCTURED TABLE Excel

www.msofficeforums.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[EmmeEnne1979]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

SUMPRODUCT and SUBTOTAL for STRUCTURED TABLE

SUMPRODUCT and SUBTOTAL for STRUCTURED TABLE Excel

www.msofficeforums.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

OK got it. Thanks

 

[shift-del]

So, you got an answer yesterday 01:33 PM and still asked the same question today?

 

[arthurbr]

Also cross posted at SUMPRODUCT and SUBTOTAL for STRUCTURED TABLE

 

[EmmeEnne1979]

Sorry I did not get any notice about the answers in SUMPRODUCT and SUBTOTAL for STRUCTURED TABLE.

Thanks for your help