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)))
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
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?
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).
