Running Count of Unique Values in a Column Based on Value of Another Column

Question

=COUNT(UNIQUE(FILTER($D$2 : D2,&nbsp; $Q$2 : Q2 = Q2)))

coincornergib · Answer

=SUM(1/FILTER(COUNTIFS($J$19:J19, $J$19:J19),$K$19:K19=K19)) is where I head to first, but it doesn't work

nikolinode · Answer

=SUMPRODUCT((($A$2:$A2=A2)*($B$2:$B2="PA")*($C$2:$C2=0))*(($A$2:$A$100=A2)*($B$2:$B$100="CP")))

coincornergib · Answer

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!

nikolinode · Answer

CoinCornerGib&nbsp;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.

coincornergib · Answer

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.

Forum Discussion

Running Count of Unique Values in a Column Based on Value of Another Column

Share

Resources