Help automating an excel formula using Alteryx

kunalkhanna1132

The Excel formula =IF(D100="","",+IF(ISERROR(SEARCH("&",N100)),IF(COUNTIFS($O$2:O100,O100,$D$2:D100,"Yes")+COUNTIFS($O$2:O100,O100,$D$2:D100,"No")>1,0,1),"Check")) is a conditional statement that performs several checks and calculations to determine the output for each row. 

I want to automate this column's formula and this is what AI had given me. I don't have any data to check this workflow. Can anyone please check if this would work? If anyone has someother idea to automate this without multirow tool, it would be more helpful.

Step-by-Step Workflow Logic Step 1: Input Data

• Add Input Data Tool:
  • Drag an "Input Data" tool onto the canvas.
  • Select File and Sheet: Configure the tool to import the relevant data, ensuring you select the columns D, N, and O.

Step 2: Multi-Row Formula Tool for Count

• Add Multi-Row Formula Tool:
  • Drag a "Multi-Row Formula" tool onto the canvas.
  • Configure Multi-Row Formula:
    • Create a new column to store the cumulative count of "Yes" and "No" for each value in column O.
    • Use the following logic:
      plaintext

      IF [Row-1:O] = [O] THEN [Row-1:Count] + (IF [D] = "Yes" OR [D] = "No" THEN 1 ELSE 0 ENDIF) ELSE (IF [D] = "Yes" OR [D] = "No" THEN 1 ELSE 0 ENDIF) ENDIF

Step 3: Formula Tool for Result

• Add Formula Tool:
  • Drag a "Formula" tool onto the canvas.
  • Configure Formula:
    • Create a new column for the result.
    • Use the following logic:
      plaintext

      IF ISNULL([D]) THEN "" ELSEIF CONTAINS([N], "&") THEN "Check" ELSEIF [Count] > 1 THEN 0 ELSE 1 ENDIF

Step 4: Output Data

• Add Output Data Tool:
  • Drag an "Output Data" tool onto the canvas.
  • Configure Output: Specify the output file and format to save the results.

Explanation of the Alteryx Workflow

• Multi-Row Formula Tool: Calculates the cumulative count of "Yes" and "No" for each value in column O.
• Formula Tool: Applies conditional logic to determine the result for each row, ensuring all rows are present in the output.

GrowthNatives

Hi @kunalkhanna1132 , i understand ypu are trying to duplicate and automate a function you created in excel.

✅ Validation of Alteryx Workflow

Now let’s validate whether the described Alteryx workflow matches this logic:

🔹Step 1: Input Tool

No issues here — just make sure the columns D, N, and O are present.

🔹Step 2: Multi-Row Formula Tool (Count "Yes"/"No" in D grouped by O)

The provided logic:

IF [Row-1:O] = [O] THEN 
   [Row-1:Count] + (IF [D] = "Yes" OR [D] = "No" THEN 1 ELSE 0)
ELSE 
   (IF [D] = "Yes" OR [D] = "No" THEN 1 ELSE 0)

🚫 Issue: This is incorrect logic for cumulative COUNTIFS behavior.

• Excel uses COUNTIFS over the entire range from row 2 to current row, not just cumulative values from previous row.

• The Multi-Row Formula tool can’t replicate COUNTIFS without first sorting and using a Running Total grouped by value in column O.

✅ Better Approach (Without Multi-Row Formula Tool):

Use Summarize + Join + Formula, like this:

💡 Recommended Alteryx Approach (No Multi-Row Tool Needed) Step 1: Add Filter Tool

• Filter where [D] = "Yes" or [D] = "No"

Step 2: Create a RecordID

• Use Record ID Tool to track row position.

Step 3: Create Intermediate Lookup Table

• Use Join Tool:

  • Join Original Data with Filtered Data.

  • Join on O values.

  • Only keep where [Right_RecordID] <= [Left_RecordID].

• Then, Summarize the matched data:

  • Group by [Left_RecordID]

  • Count rows

This gives you a COUNTIFS-like column.

Step 4: Join back to the original dataset using [RecordID] Step 5: Use Formula Tool for Final Logic

IF ISNULL([D]) THEN ""
ELSEIF CONTAINS([N], "&") THEN "Check"
ELSEIF [CountYesNo] > 1 THEN 0
ELSE 1

">⭐Hope this solution helps you make the most of Alteryx! If it did, click 'Mark as Solution' to help others find the right answers.

💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!

🚀Let’s keep building smarter, data-driven solutions together! 🚀 [Explore more]