Forum Discussion
Excel formula to count and sum when value changes?
I used two helper columns. Here are the formulas I used:
C1: =SUM(D1:E1)
D1: =LET(items,TEXTSPLIT(A1,","),itemsNext,HSTACK(DROP(items,,1),0),delta,DROP(itemsNext-items,,-1),-SUM(IF(delta<0,delta,0)))
E1: =LET(items,TEXTSPLIT(A2,","),itemsNext,HSTACK(DROP(items,,1),0),delta,DROP(itemsNext-items,,-1),SUM(IF(delta>0,delta,0)))
C2: =SUM(D2:E2)
D2: =LET(items,TEXTSPLIT(A1,","),itemsNext,HSTACK(DROP(items,,1),0),delta,DROP(itemsNext-items,,-1),SUM(IF(delta>0,delta,0)))
E2: =LET(items,TEXTSPLIT(A2,","),itemsNext,HSTACK(DROP(items,,1),0),delta,DROP(itemsNext-items,,-1),-SUM(IF(delta<0,delta,0)))
- Wishing_WellJul 30, 2025Copper Contributor
Thank you for your response.
A1 and B1 are continuously changing cells populated by a live data feed.....I gave an example of how the cells might change every second. I am not sure how TEXTSPLIT would work in this case
- JKPieterseAug 01, 2025Silver Contributor
OK, misunderstood. I though the values in cells A1 and B1 were as you posted them: "10,16,8,12,3". Hence my TEXTSPLIT function example.
Definitely needs some (VBA?) programming.
How does the data end up in cells A1 and B1 precisely?
- Patrick2788Jul 30, 2025Silver Contributor
You would need a way to maintain a history of the data coming into the sheet. I don't believe vba would be of use here (or it would at least get very messy). You might look into Data Streamer where you could pull live data from an import file or connect Excel directly to an external device (if that applies to your setup).