A totals function which works using filters

[mfairhurst1]

Hi everyone

I hope you are well!

I am trying to get a formula (at least I think this one is a formula issue) which will sum a set of cells depending upon what data is filtered. These total also have to be in a cell which allow me to add more data to the rows.

Please see excel sheet provided and have a go if you wish.

Thanks

Mike

Forecast Totals.xlsx

[Ace_XL]

Try

=SUBTOTAL(9,C2:C5)

[daffodil11]

I went with:
=SUBTOTAL(9,INDIRECT("B2:B"&ROW(B10)-1))
=SUBTOTAL(9,INDIRECT("C2:C"&ROW(C10)-1))

The INDIRECT function will allow you to insert rows and not have to worry about adjusting the references.

[mfairhurst1]

Yes that works but if I insert a new row then the formula becomes redundant. If I put the formula =SUBTOTAL(9,C:C) this works but only in another cell as I am circular referencing as I will be totalling the total. Is there any way round this?

[mfairhurst1]

Thats brilliant. Thanks for both your responses they are much appreciated!!!!!!

Mike

[daffodil11]

Sorry, in my example I had the subtotals considerably lower:

=SUBTOTAL(9,INDIRECT("B2:B"&ROW(B6)-1))
=SUBTOTAL(9,INDIRECT("C2:C"&ROW(C6)-1))

The formulas should update automatically to any inserted rows.

The row references above should be the same as the cell you're putting them in, wherever they are.