Forum Discussion

CoinCornerGib's avatar
CoinCornerGib
Copper Contributor
Sep 26, 2023
=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's avatar
NikolinoDE
Gold Contributor
Sep 26, 2023
=SUMPRODUCT((($A$2:$A2=A2)*($B$2:$B2="PA")*($C$2:$C2=0))*(($A$2:$A$100=A2)*($B$2:$B$100="CP")))
  • CoinCornerGib's avatar
    CoinCornerGib
    Copper Contributor
    Sep 26, 2023
    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's avatar
      NikolinoDE
      Gold Contributor
      Sep 26, 2023

      CoinCornerGib 

      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's avatar
        CoinCornerGib
        Copper Contributor
        Sep 26, 2023

        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.

Resources