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

Copper Contributor

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

=COUNT(UNIQUE(FILTER(\$D\$2 : D2,  \$Q\$2 : Q2 = Q2)))

5 Replies

# Re: Running Count of Unique Values in a Column Based on Value of Another Column [Difficult!]

=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

# Re: Running Count of Unique Values in a Column Based on Value of Another Column [Difficult!]

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

# Re: Running Count of Unique Values in a Column Based on Value of Another Column [Difficult!]

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!

# Re: Running Count of Unique Values in a Column Based on Value of Another Column [Difficult!]

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.

# Re: Running Count of Unique Values in a Column Based on Value of Another Column [Difficult!]

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.