Using Aggregate and Index Functions

[francoiscj1]

I have an office inventory spreadsheet and the following is what I would like to do:

• Get a running total for quantity even if I sort any column the running total should not break
• Get Running total sorted by item and should reset when the item name changes
• Get running total (for visible rows only) when the table is filtered

I know I can do this using aggregate and index but I can't find a formula online that would work to help me. .

 

[Aladin Akyurek]

[1]
=SUBTOTAL(9,QuantityRange)

[2]
=SUMPRODUCT(SUBTOTAL(9,OFFSET(SumRange,ROW(SumRange)-ROW(INDEX(SumRange,1,1)),0,1)),--(ItemRange=Item))

 

[francoiscj1]

Thanks for answering Aladin, but I don't understand the answers you gave. Are you giving me two options for my question?

 

[Aladin Akyurek]

Thanks for answering Aladin, but I don't understand the answers you gave. Are you giving me two options for my question?

"Get running total (for visible rows only) when the table is filtered"

"Get a running total for quantity even if I sort any column the running total should not break"

=SUBTOTAL(9,QuantityRange)

"Get Running total sorted by item and should reset when the item name changes"

=SUMPRODUCT(SUBTOTAL(9,OFFSET(SumRange,ROW(SumRange)-ROW(INDEX(SumRange,1,1)),0,1)),--(ItemRange=Item))

The last one needs a arnge to sum (hence SumRange).

If these are not what you are looking for, please elaborate.