Combining SUMIFS and SUMTOTAL

[Roo1799]

Hi,

I'm trying to combine SUBTOTAL with SUMIFS using SUMPRODUCT but the formula is returning zero and I cannot figure it out!

=SUMPRODUCT(SUBTOTAL(9,OFFSET(Fcst_scenarios!I2,ROW(Fcst_scenarios!I:I)-ROW(Fcst_scenarios!I2),0)),(Fcst_scenarios!AB:AB=AT!C12)&(Fcst_scenarios!A:A=AT!D9)+0)

Sum values are coming from Fcst_scenarios Column I
The 2 criterias to meet are in cell AT!C12 & AT!D9 - with the range being in Fcst_scenarios Column AB and A.

Currently this formula is only working if I take out the second criteria "&(Fcst_scenarios!A:A=AT!D9)"

The data is linked to slicers hence the need for a SUMTOTAL.

Any help would be greatly appreciated!

Thanks!

 

[Fluff]

Change the & to an *
Although if you are happy to use a helper column, you can use a simple sumifs

 

[Roo1799]

Great! Thanks for your help and super quick response!

 

[Fluff]

Are you happy to use a helper column, rather than volatile functions?

 

[Roo1799]

Are you happy to use a helper column, rather than volatile functions?

Apologies for my ignorance, but by helper column, do you mean splitting the formula into 2?

 

[Fluff]

Sort of. You can put

=SUBTOTAL(3,A2)

in the first blank column on the Fcst_scenarios sheet, changing the A2 to a column that will always have data.
Then you can use something like

=SUMIFS(Fcst_scenarios!I:I,Fcst_scenarios!AB:AB,AT!C12,Fcst_scenarios!A:A,AT!D9,Fcst_scenarios!AC:AC,1)

Changing the AC to whatever column has the subtotal formula.

 

[Roo1799]

Thanks for this, really sped up the workbook. My only issue now thou is that when the filters change in the fcst scenarios sheet the formula doesn't reflect the new totals?

 

[Fluff]

Which cells did you put the subtotal in & what is your current formula for the sumifs?

 

[Roo1799]

current formula is SUMIFS(Fcst_scenarios!$I:$I,Fcst_scenarios!$AB:$AB,AT!$C12,Fcst_scenarios!$A:$A,AT!$D$9,Fcst_scenarios!$AS:$AS,1))

Column AS in the fcst_Scenarios sheet has the formula =SUBTOTAL(3,A2)

 

[Fluff]

That should work.
Will col A always have a value on every row?
Also how are you filtering the data?