•3 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How to create a row-by-row running total for a dynamic array?
Our take
Are you feeling weighed down by the limitations of traditional spreadsheets? Creating a row-by-row running total for a dynamic array can seem daunting, especially when each total needs to begin with an initial value from the same row. But with the right formula, you can transform your data analysis into a streamlined, efficient process. In this guide, we’ll break down how to harness powerful functions like SCAN, BYROW, and MAKEARRAY, so you can generate the results you need effortlessly.
Hello everyone,
I'm trying to create a single, dynamic formula that calculates a running total along each row. Each row's running total needs to start with a specific initial value from that same row.
Here is a simplified version of my data and the desired result.
Requirements table
| Material | D 2026-02-05 | D 2026-02-06 | D 2026-02-07 | D 2026-02-08 | D 2026-02-09 |
|---|---|---|---|---|---|
| 123 | -265 | -684 | -734 | -553 | -501 |
| 234 | -953 | -771 | -241 | -266 | -697 |
| 345 | -175 | -247 | -516 | -795 | -762 |
| 456 | -655 | -635 | -456 | -196 | -502 |
| 567 | -192 | -708 | -492 | -471 | -273 |
| 678 | -448 | -567 | -636 | -712 | -289 |
| 789 | -224 | -358 | -967 | -996 | -876 |
Table formatting by ExcelToReddit
Relevant materials table
| Material | Current Inventory | D 2026-02-05 | D 2026-02-06 | D 2026-02-07 | D 2026-02-08 | D 2026-02-09 |
|---|---|---|---|---|---|---|
| 123 | 485 | |||||
| 234 | 141 | |||||
| 345 | 430 | |||||
| 456 | 415 |
Desired solution
| Material | Current Inventory | D 2026-02-05 | D 2026-02-06 | D 2026-02-07 | D 2026-02-08 | D 2026-02-09 |
|---|---|---|---|---|---|---|
| 123 | 485 | 220 | -464 | -1198 | -1751 | -2252 |
| 345 | 141 | -34 | -281 | -797 | -1592 | -2354 |
| 567 | 430 | 238 | -470 | -962 | -1433 | -1706 |
| 789 | 415 | 191 | -167 | -1134 | -2130 | -3006 |
Table formatting by ExcelToReddit
I've tried several advanced array formulas but keep hitting roadblocks:
- SCAN: This works perfectly for a single hardcoded row
=SCAN(B2,J2:N2,LAMBDA(a,b,a+b))), but isn't dynamic. - BYROW: When I try to make it dynamic
=BYROW(SCAN(B2,J2:N2,LAMBDA(a,b,a+b)))), it fails with a #CALC error because BYROW's LAMBDA expects a single value, but SCAN returns a whole array for each row so, nested arrays error. - MAKEARRAY: This feels like the right tool. My first attempt correctly looked up the data but didn't accumulate the total; it just added the starting inventory to each day's value individually.
=LET( materials,A2:A5, startValues,B2:B5, relevantData,INDEX(J2:N8,XMATCH(A2:A5,I2:I8),XMATCH(C1:G1,J1:N1)), result,MAKEARRAY(ROWS(relevantData),COLUMNS(relevantData),LAMBDA(r,c,INDEX(startValues,r)+INDEX(relevantData,r,c))), result) My final attempt was to create a running sum inside the MAKEARRAY, but it failed with a #VALUE! error. I now understand this is because my syntax for creating the range to SUM was incorrect SUM(INDEX(relevantData,r,c):c).
I'd appreciate your help
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Summarise 2D Dynamic Array?Using Excel 365 on Windows 11 This should be simple, but I haven’t manged to find a way to do it (and Copilot has just generated a bunch of #REF! and #CALC! errors…) I’m trying to do some basic forecasting over multiple years, but using dynamic ranges so the start and end dates, and the number of categories forecast can update automatically. I can get to a dynamic forecast by month, but am drawing a blank when I try to summarise that by year. A simplified version of the sheet currently looks like this: https://preview.redd.it/li08zsp8w7rg1.png?width=584&format=png&auto=webp&s=2d61ac0c1d887bb3b18bb3f08a9542fbc72f439b Data entry in B4:E10 Months dynamic range (pink) calculated in C14 =EOMONTH(D2,SEQUENCE(1,F2,0,1)) and spills C14 to AL14, or wherever the last month is. Categories dynamic range (pale blue) calculated in B15 =FILTER(B4:.B10,B4:.B10<>"")) and spills B15 to B20 here. Years dynamic range (peach) is just a helper row =YEAR(C14#). It might not even be necessary other than visually. The Forecast dynamic range (green) is then =XLOOKUP($B15#,$B$4:.$B$10,$C$4:.$C$10,0)*(C14#>=XLOOKUP($B15#,$B$4:.$B$10,$D$4:.$D$10,0))*(C14#<=XLOOKUP($B15#,$B$4:.$B$10,$E$4:.$E$10,0)) All I want to do is put one formula in C25 to calculate the blue dynamic range total by year for each category. The years are a dynamic range (UNIQUE of C14 above) and the categories are just B15#. This it turns out is completely beyond me - I can calculate each row individually using SUMIFS quite easily, but cannot persuade it to calculate one SUMIF for each line using one formula Anyone got a good way to deal with this? Thanks. submitted by /u/sprainedmind [link] [comments]
- Is there a way to reference an array of arrays?Is there a way to get Excel to use an array of arrays in in a formula? Instead of writing a formula that references an array and dragging it down through a column, can I write the formula in a way that results in an output of SUM(O2#),SUM(O3#),SUM(O4#),SUM(O5#),...,SUM(O1000#)}? I work with enormous data sets, but they vary in size. Since I need the workbooks to work with any amount of data, I end up dragging formulas down extra far, leaving rows that just output empyy values when the data doesn't reach that far. If I can make the formula reference an array of arrays then I could eliminate all those garbage rows. Thank you. Unfortunately, because a security restrictions, I can't use macros or VBA at all. I need to do it everything through standard excel syntax. EDIT: Sorry, I didn't make it clear that I used SUM() as an example to try and simplify and generalize. Here's a more explicit example of what I'm doing: ID Data Col C Col D A 14 =UNIQUE(ID#) =IF($C1="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C2,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) A 23 =IF($C2="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C2)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) A 42 =IF($C3="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C3,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) B 1 =IF($C4="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C4,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) B 2 =IF($C5="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C5,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) B =IF($C6="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C6,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) B =IF($C7="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C7,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) B 90 =IF($C8="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C8,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) B 94 =IF($C9="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C9,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) =IF($C10="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C10)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) ... =IF($C1000="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C1000,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) Output: ID Data Col C Col D Col E Col F F A 14 A 14 23 42 A 23 B 1 2 90 94 A 42 "" B 1 "" B 2 "" B "" B "" B 90 "" B 94 "" "" ... "" There are a lot of data manipulations omitted, so if something seems weird (like the empty cells in Data Col) please ignore it. The questions is how to eliminate the unneeded rows in Col D. Thank you. EDIT2: Studying the BYROW, SCAN, and MAP suggestions, led to this page on Exceljet, https://exceljet.net/glossary/array-of-arrays , that suggests my question is a known problem in Excel's engine and there is no work-around. Thank you to everyone who tried to help. submitted by /u/TwitchyDingo [link] [comments]
- Multiplying a colum by the dynamic sum of other columns...for multiple rows in one cellHi, I am trying to multiply amounts by percentages that accumulate over time. For example in the table below: how much did I pay total by the end of year 2 (including year 1) for all the items. Same for year 3... I have way too many items to do it manually! I am guessing it will be some kind of sumprod but can't figure out how to formulate it! To make things even more interesting, I have a condition to add to pick only some of the items (for example: calculate only for the items having the word "wood" in it). Item Cost Percentage paid year 1 Percentage paid year 2 Percentage paid year 3 Percentage paid year 4 1 4 000 $ 5% 10% 25% 60% 2 5 000 $ 10% 15% 20% 3 … … … … … submitted by /u/cran11 [link] [comments]
- Running count recommended FormulaI am trying to get a running total on daily log qty. I can't seem to get a formula to work on (Total's Sheet) I want to be able to add to "daily log" and "Totals sheet populates using a Costcode as the cross reference and sums up Qty. By the end of I will have about 40 Cost codes and around 1k entries. What formula is best suited for this? I have tried to use Xlookup, VLookup, and sumif. I can't get any of them to work for me only an error, or "0" populates. Can someone break this down to me in simple terms why one formula is better than the other and when to use it in a situation like this. (Daily Log) Date/Lead Name/ Cost Code/ Description/ UnitMeasure/Price/QTY/Revenue (Totals) Costcode/ Description/UnitMeasure/QTY Thanks submitted by /u/KiloMarshBoxin [link] [comments]