Forum Discussion
Re: Running Count of Unique Values in a Column Based on Value of Another Column
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!
2 Replies
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.
Hello again Niko, unfortunately the formula doesn't work. I changed the reference from UniqueTxID (as these are all unique to the row, not the transaction) to TxID.
Also, the condition of PA = 0 does not mean a transaction is unsuccessful. A transaction is only unsuccessful if the TxID of a PA is not followed by a CP for that same TxID.
I appreciate your efforts and will attempt to apply the SUMPRODUCT boolean logic in order to solve the issue.
Thank you.
