cumulitive Totals and sort

[Laura]

How would I enter a forumla to provide a cumulitive total column yet still be
able to sort data in a variety of ways. I already apply filters, but
sometimes I need to sort the entire worksheet in a variety of ways

My worksheet captures projects that need funding.
Column
A = funding status
B = Office
C = project title
D = Priority
E = Amt
F = Cumualitve total

Sometimes I need the data sorted by Office, sometimes by Priority, and
sometimes by status. Yet whenever I change the sort, i lose the cumulative
total.

I know this sounds greedy, but should answers be provided I would also
appreciate brief explanation of why it will work this way. If I understand
the answer, I'm better able to remember it .
Thanks
Laura

 

[Pete_UK]

You could try something like this in F2:

=IF(ISNUMBER(F1),E2+F1,E2)

and copy this down, assuming that you have headers in row 1. When the
data is sorted, whatever is in row 2 will just take the value of E2 as
the starting point for the cumulative total, whereas every other row
will add the value in E onto the running total.

Hope this helps.

Pete

 

[Laura]

brilliant! Thank you. Works exactly as I asked.

Now what if i had asked about FILTER instead of SORT? Cause when I filter,
the cumm total is not right.

Thanks
Laura

 

[Pete_UK]

Thanks for feeding back, Laura.

This seems to work for both a SORT and a FILTER on some test data I
set up to mimic your situation:

=IF(ISNUMBER(F1),SUBTOTAL(9,E$1:E2),E2)

Enter the formula in F2 and copy down as necessary.

Hope this helps.

Pete

 

[Laura]

Dear Pete,
You are brilliant! Works perfectly! Thank you!

Laura

 

[Pete_UK]

Glad it worked for you, Laura. Thanks for letting me know.

Pete