COUNTIFS from Excel to Alteryx

[jwardin]

Hi,

I need help converting a formula from Excel to Alteryx. 

The formula I would use in Excel is:

=IF(J3="","",IF((COUNTIFS(J:J,J3,K:K,K3)-COUNTIFS(J:J,J3,K:K,K3*-1))-(COUNTIFS(J:J,J3,K:K,K3)-COUNTIFS(J3:J524287,J3,K3:K524287,K3))<=0,"Offset",""))

I am having a hard time thinking about how to recreate this formula in Alteryx. I have tried the Summarize tool however I am hitting a roadblock. 

Any suggestions are appreciated. 

[Luke_C]

Hi @jwardin - can you attach some sample data? Might help get to a resolution faster. I'd imagine you will need to use a few summary tools.

[jwardin]

Thank you for assisting @Luke_C  

I have attached a sample dataset. I am attempting to re-create the formula used in columns L, O, and R. I have highlighted these headers orange. 

The purpose of this formula is to determine offsetting transactions in the data. Let me know if there is any other information that would be helpful.

Thanks!

[clmc9601 · Accepted solution]

Hi @jwardin,

Alteryx handles data differently than Excel: Alteryx can reference any column in a single row and apply those changes to every row in the dataset, whereas Excel can manipulate single cells, groups of cells, entire rows, or entire columns all in the same formula. These formulas needed to count values in the entire dataset, which is where the Summarize tool comes in.

Is this what you're going for? You can replicate it per column or put it in a macro to reuse.

If this helps, please consider marking it a solution so others may find it.

[jwardin]

After applying this solution, I noticed that the sum of Offsets did not sum to 0, as such the routine is incorrectly identifying offsets when there are 3 of the same LE2 amounts per Concatenate. for example: 100, 100, and (100).

Do you know if there is a simple fix for this? Thank you in advance for the help