# Combining SUMIFS and SUMTOTAL

#### Roo1799

##### New Member
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!

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Change the & to an *
Although if you are happy to use a helper column, you can use a simple sumifs

Great! Thanks for your help and super quick response!

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

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?

Sort of. You can put
Excel Formula:
``=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
Excel Formula:
``=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.

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?

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

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)

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

Replies
4
Views
191
Replies
4
Views
149
Replies
1
Views
68
Replies
3
Views
380
Replies
2
Views
385

1,203,625
Messages
6,056,393
Members
444,862
Latest member
more_resource23

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back