COUNTIFS from Excel to Alteryx

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.

Developer

Developer Tools

Macros

Help

Accepted answers

clmc9601

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.

excel_offset_formulas.yxmd

All comments

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!

Sample Data Set SumIFS.xlsx

clmc9601

Hi @jwardin,

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.

excel_offset_formulas.yxmd

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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280