CODEWITHSUNDEEP
excel
Glenn
Glenn

Reputation: 15

Excel table running total formula based on two columns

My Excel table has a deposits amount column and a payments amount column. I need a formula for a running total column that will work even if I filter out some of the rows of data.

Upvotes: 0

Views: 5045

Answers (1)

William
William

Reputation: 997

The subtotal formula allows you to perform an aggregate operation against only the visible cells in the range.

Assuming your deposits start in cell A2 and payments in B2, you would enter the formula =SUBTOTAL(109,$A$2:A2,$B$2:B2) in C2 to start the running total. Use autofill to copy the formula down for each row.

109 represents the SUM function. $A$2:A2,$B$2:B2 are references to your deposit and payment columns. Autofill will dynamically resize the references when copied.

Assumption: Values in your payment column are negative. If not, use =SUBTOTAL(109,$A$2:A2)-SUBTOTAL(109,$B$2:B2) instead of the above formula.

enter image description here enter image description here

Upvotes: 1

Related Questions