combining Sumifs and Subtotal

[juca73]

Morning,

Currently i have the following formula

=SUMIFS(Data!$M$7:$M$3440,Data!$M$7:$M$3440,">"&Sheet1!B5,Data!$M$7:$M$3440,"<"&Sheet1!C5)

M7:M3440 is the column where the sum is found
B5 is the cell that contains the lower number
C5 is the cell that contains the higher number

The above works perfectly however, when i filter the columns in the data sheet i want the figures to adjust accordingly.

I think it will be a combination of sumproduct and subtotal but struggling with the higher and lower than functions in formula.

Thanks in advance

 

[juca73]

Morning,

Currently i have the following formula

=SUMIFS(Data!$M$7:$M$3440,Data!$M$7:$M$3440,">"&Sheet1!B5,Data!$M$7:$M$3440,"<"&Sheet1!C5)

M7:M3440 is the column where the sum is found
B5 is the cell that contains the lower number
C5 is the cell that contains the higher number

The above works perfectly however, when i filter the columns in the data sheet i want the figures to adjust accordingly.

I think it will be a combination of sumproduct and subtotal but struggling with the higher and lower than functions in formula.

Thanks in advance

Sorry formula should be

=SUMIFS(Data!$AE$7:$AE$3440,Data!$M$7:$M$3440,">="&Sheet1!B5,Data!$M$7:$M$3440,"<="&Sheet1!C5)

AE7:AE3440 is the column to be totalled

 

[Fluff]

I would add a helper column to the data sheet using

=SUBTOTAL(3, A7)

change the A7 to a column that will always have data & then you can use

=SUMIFS(Data!$AE$7:$AE$3440,Data!$M$7:$M$3440,">="&Sheet1!B5,Data!$M$7:$M$3440,"<="&Sheet1!C5,Data!$AF$7:$AF$3440,1)

changing column AF to the column with the subtotal function.