Excel - Best Way For Running Totals - Episode 2590 

I use that SUM function with ignoring text. I love that unsophisticated one lol

Dear Bill, With the new dynamic SCAN function it is much easier, and the result spills over: =SCAN(0,D5:D13,LAMBDA(x,y,x+y)) 🤗

Super interesting survey!!✌ Remembered, before having SCAN, how I did this, single cell, using MMULT -for running totals =LET(a,B2#,r,ROWS(a),s,SEQUENCE(r),MMULT(IF(s>=SEQUENCE(,r),TRANSPOSE(a),0),s^0)) -and even running "subtraction" 😉 =LET(a,B2#,r,ROWS(a),s,SEQUENCE(,r),TRANSPOSE(MMULT(s^0,IF(SEQUENCE(r)>=s,a,0)))) What is interesting with this is that because of its "squareness" mmult concept, excel runs out of resources for vectors larger than 7327 elements. Believe it or not, Excel largest square sequence that can "print" or calculate is only =SEQUENCE(7327,7327) Anyhow, SCAN is fast and easy with a single initial acumulator and an array, the challenge is to reset the initial value by row or by column for 2D super large arrays. Have a study on this one, one day will get published at the forum. Again, Super cool to test speeds of different methods. ✌🙏

I use running totals in tables in excel as I typically need table structure with index column to analyze account activity. I use the Sum(header row in column:first value in column) from Jon at Excel Campus. This application works well for adding/deleting items in the table without bastardizing the runttl function. That said, I love the simplicity of what you demonstrated.

Very interesting Bill, thanks. I'll just SCAN the comments for any differing suggestions.

I love the =SUM(E4,D5) tip! Simplicity is best! My biggest beef with this type of running total, though, is that inserting or deleting rows causes cell reference issues. To overcome that, I use a relative cell reference in Name Manager called CellAbove. Then my running total would be =CellAbove+D5, and my first row would be a hard keyed “opening balance” (when working with accounting data) to avoid the different formula. Now I should be able to just use =SUM(CellAbove,D5). Thanks!!!

Hi One more way with one formula adding cell above In the header cell, type 0 and change the custom formatting to display the header description in all the categories for positive values; negative values; zero values; text values. Then just add the value to the left to the value above

This works well in a range, and it works especially well in an Excel Table (where the cells are structured references). E5 =N(OFFSET(E5,-1,))+D5

Fun and informative video, Mr Excel!!!!

Amazing. Thanks for the share.

Thank you for your video Bill, very interesting. I like the Scan/Lambda combination although sometimes it does not suit your situation. For example, when producing the running total for sales for the month and you have 5 days to go the formula repeats the last value for the remaining cells. I had to resort to this: =SCAN(0,C8#,LAMBDA(a,b,IF(VALUE(OFFSET(b,-6,))

00:28 - Bill, I have never been annoyed by any of ur content.

Thanks Bill. One additional point for consideration. I know you don't like tables but if this is being done inside a table (A1:B10) where A is number and B is running total the formula for column B = SUM (A2,B1) will break (and the user probably won't know it) if the user inserts a row in the the middle of the table (lets say new row = 5) because the formula in the new row will be correct (SUM(A5,B4) but row 6 will not adjust accordingly but instead will be SUM(A6,B4). To combat this I've moved to using the formula =SUM(A2,OFFSET(B2,-1,0)) which will still be accurate if a row is inserted in a table. It loses points on "explainable to someone else" criteria but better than breaking and still much faster than SUM(A$2:A2).

Thank you Bill for this interesting Video

In a Table (vitually all my data sets are in Tables) click on header, then insert a colon, then click on the cell below the header, then wrap the expsnding range in SUM (or SUMIFS, etc). Really fast to set up.

Very interesting analysis of which formula works faster. I often use the Mr. Excel way. However, I will consider going back to Bill kindergarten way. It just makes more sense even if my spreadsheets are relatively small by comparison of 100,000 rows.

@Bill I do not have the add-in to calculate the speed, what if you wrap Carlo's formula in a LET statement =LET(a,INDEX($D$5:$D$13,1),SOMME(a:D5)), so that it will calculate the INDEX($D$5:$D$13,1) only once ?

Thank you Mr. excel! I'm curious about the time that the SCAN solution posted by someone below may takes compared to the other methods

This only refers to 2 cells and is easy to explain. Too slow? E5=N(E4)+D5

Like a few others who have commented, I like SCAN, but suspect it would come second on your scoring system

But can you create a formula that takes over an hour to do 100,000 cells?

=SCAN(0,rng,LAMBDA(a,b,a+b))

Bill K's way won't work when we're sorting the data. Right?

Can you do one video showing the splll vs copying the formula😜

Running totals in Pivot Table. The data exist in its own Table, not range. A pivot table or other reporting methods report on that data, not in that data!

I’ve been working on formula yesterday to reset an Index number when the data changes in any of three columns =IF(AND(Q36=Q35,O36=O35,M36=M35),R35+1,1), I'm trying to get it to spill but it's getting the better of me.