VBA or Formula for Running Total

[stwp86]

Hey Everyone . . .

Looking for some assistance on a formula or VBA. I have the below dataset and I am trying to automate the calculation of the running total. Basically, I need it to say, if the user stays the same, the processID stays the same, the client id is new, and the closed column is 0 the increment my running total. But if the same client comes up (row 4 for example) I don't want to increment my running total. Also, if closed is equal to 1 I need to decrease the running total appropriately, so for the first 3 rows I see the total go from 0 to 1 to 2 to 3, but on the third row they closed, so I need to the running total at 2. Thoughts?

thanks!


[TABLE="class: grid, width: 357, align: left"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]User[/TD]
[TD]Closed [/TD]
[TD]ClientID[/TD]
[TD]processID[/TD]
[TD]Running Total[/TD]
[/TR]
[TR]
[TD]User1[/TD]
[TD]0[/TD]
[TD]8753053[/TD]
[TD]8696[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]User1[/TD]
[TD]0[/TD]
[TD]10193883[/TD]
[TD]8696[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]User1[/TD]
[TD]1[/TD]
[TD]4905128[/TD]
[TD]8696[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]User1[/TD]
[TD]0[/TD]
[TD]10193883[/TD]
[TD]8696[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]User1[/TD]
[TD]1[/TD]
[TD]6738817[/TD]
[TD]8696[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]User1[/TD]
[TD]1[/TD]
[TD]10193883[/TD]
[TD]8696[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]User1[/TD]
[TD]0[/TD]
[TD]10193883[/TD]
[TD]8696[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]User1[/TD]
[TD]0[/TD]
[TD]10066793[/TD]
[TD]8696[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]User1[/TD]
[TD]1[/TD]
[TD]10066793[/TD]
[TD]8696[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]User1[/TD]
[TD]1[/TD]
[TD]10193883[/TD]
[TD]8696[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[mbt2103]

Why doesn't the running total increase from 2 to 3 on row 4? User is same, closed is 0, client is different, and processID is same.

Based on your explanation, the following equation works:
=IFERROR(IF(AND(A2=A1,D2=D1,C2<>C1),E1+1,E1)+IF(B2=1,-1,0),1)
Just paste it in E2 and autofill it down.

 

[AlphaFrog]

<br />

Book1
	A	B	C	D	E
1	User	Closed	ClientID	processID	Running Total
2	User1	0	8753053	8696	1
3	User1	0	10193883	8696	2
4	User1	1	4905128	8696	2
5	User1	0	10193883	8696	2
6	User1	1	6738817	8696	2
7	User1	1	10193883	8696	1
8	User1	0	10193883	8696	1
9	User1	0	10066793	8696	2
10	User1	1	10066793	8696	1
11	User1	1	10193883	8696	0
Sheet1

Cell Formulas
Range	Formula
E3	=AND(A3=A2,COUNTIF($C$2:C3,C3)=1,D3=D2)-B3+E2

Put a 1 for the first running total value in E2.
Copy the E3 formula down column E for the other running totals.

 

[stwp86]

ClientID is the same as a previous one, so it is not net new.

Why doesn't the running total increase from 2 to 3 on row 4? User is same, closed is 0, client is different, and processID is same.

Based on your explanation, the following equation works:
=IFERROR(IF(AND(A2=A1,D2=D1,C2<>C1),E1+1,E1)+IF(B2=1,-1,0),1)
Just paste it in E2 and autofill it down.

 

[stwp86]

Looks like this accomplishes what I need, thanks AlphaFrog