Forum Discussion
Excel formula to count and sum when value changes?
As a newbie to VB and excel I am having problem with the following, any and all help greatly received:
Cell A1 is being populated by a data feed and is frequently changing (every second) in value, both up and down e.g. 10,16,8,12,3
Cell B1 is also being populated by a data feed and is frequently changing (every second) in value both up and down e.g. 8,1,6,12,19
Cell C1 needs to SUM the total amount by which A1 reduces (when it does) and by which B1 increases (when it does) - with the sequences of 10,16,8,12,3 (A1) and 8,1,6,12,19(B1)..... this would be (8+9)+(5+6+7)=35
Cell D1 needs to SUM the total amount by which A1 increases (when it does) and by which B1 decreases (when it does) - with the sequences of 10,16,8,12,3 (A1) and 8,1,6,12,19(B1)..... this would be (6+4)+(7)=17
Hopefully that makes sense!
Thank you in advance
5 Replies
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?
Use the formula combination =SUMIF(A2:A100,<>&A1,B2:B100) to calculate the cumulative sum when values change, or =COUNTIF(A2:A100,<>&A1) to count the number of changes.
