Forum Discussion
Running Count of Unique Values in a Column Based on Value of Another Column
- =SUMPRODUCT((($A$2:$A2=A2)*($B$2:$B2="PA")*($C$2:$C2=0))*(($A$2:$A$100=A2)*($B$2:$B$100="CP")))
- Hi Niko, thanks for your reply, I appreciate you taking the time to try and help. Unfortunately, without any context I'm not sure what the end goal of this formula is.
The data I gave was to provide context as to why I need this formula, but as the formula is generic and not specific to this data, I was hoping I might receive an explanation with the solution so I am able to apply it elsewhere.
Thank you!Short explanation:
The first part (($A$2:$A2=A2)*($B$2:$B2="PA")*($C$2:$C2=0)) checks if the current row matches the [User ID], has "PA" in the [PaymentType], and has a [ReasonCode] of 0. This is your condition for an unsuccessful deposit attempt.
The second part (($A$2:$A$100=A2)*($B$2:$B$100="CP")) checks if any row in the dataset (up to row 100, adjust as needed) matches the [User ID] and has "CP" in the [PaymentType], which is your condition for a successful deposit.
The SUMPRODUCT function multiplies these two conditions together. If both conditions are met for a particular row, it will result in 1 (true) for that row, and 0 (false) otherwise.
By using SUMPRODUCT, you sum up all these 1's for the rows that meet both conditions, effectively giving you a running count of successful deposit attempts for each unique [TxID] and [User ID] combination.
