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

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

On May 1, 1:36 pm, Laura wrote:
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

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" wrote:

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

On May 1, 1:36 pm, Laura wrote:
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

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

On May 4, 1:53 pm, Laura wrote:
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" wrote:
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

On May 1, 1:36 pm, Laura wrote:
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- Hide quoted text -

- Show quoted text -

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

Laura

"Pete_UK" wrote:

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

On May 4, 1:53 pm, Laura wrote:
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" wrote:
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

On May 1, 1:36 pm, Laura wrote:
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- Hide quoted text -

- Show quoted text -

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

Pete

On May 7, 11:41 am, Laura wrote:
Dear Pete,
You are brilliant! Works perfectly! Thank you!

Laura



"Pete_UK" wrote:
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

On May 4, 1:53 pm, Laura wrote:
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" wrote:
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

On May 1, 1:36 pm, Laura wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -