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] 

[kunalkhanna1132]

Hi @GrowthNatives

Thank you so much for the reply!

I have a doubt. When should I give the record Id to rows? before or after the filter tool? or is it both before and after the filter tool. Also, should I do it for both the Yes and No outputs?

[GrowthNatives]

Hi @kunalkhanna1132  , here is the detailed process. Hope it helps.

1️⃣ Add RecordID Tool Immediately After Input
- Place the RecordID tool directly after your Input Data Tool.

- This ensures you preserve the original row order, which is crucial because Excel's COUNTIFS checks "from row 2 to current row".

2️⃣ Then Apply the Filter
- Use a Filter tool after the RecordID to extract rows where

D = "Yes" or D = "No".

- This filtered data is used to simulate the COUNTIFS behavior.

3️⃣ Use the Filtered and Original Datasets in a Join
- Join the original dataset (with RecordID) to the filtered dataset (with matching O values), and only include pairs where:

Right_RecordID <= Left_RecordID

- This is how you count how many “Yes”/“No” entries occurred up to that row, grouped by value in column O.

❓Do I need to assign RecordID again after the filter?
No — you do not need to assign a second RecordID after the filter. The one assigned before the filter is sufficient to track row positions in both filtered and original datasets.

❓Should I apply this logic to both Yes and No outputs?
You only need to filter rows where D = "Yes" or D = "No" once. Then, join them with the original dataset to count how many relevant entries occurred before or at each row.

⭐Hope this solution helps you make the most of Power BI! 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] 

[kunalkhanna1132]

@GrowthNatives Do I need to join both yes and no filter outputs to the original dataset?

[GrowthNatives]

Hi @kunalkhanna1132 ,

Please refer to the screenshot.

Do you need to join both Yes and No filter outputs to the original dataset?

👉 No, you do not need to separate the Yes and No outputs and join them separately.
You can filter for both "Yes" and "No" values together using a single Filter tool like this:

[D] = "Yes" OR [D] = "No"

Why?

Because in your Excel formula, this part:

COUNTIFS($O$2:O100,O100,$D$2:D100,"Yes") + COUNTIFS($O$2:O100,O100,$D$2:D100,"No")

...counts how many rows up to the current row have the same O value AND D is either "Yes" or "No". So both values are part of a single condition.

[kunalkhanna1132]

1. Use a Union Tool to combine J and L outputs from the Join Tool:

Set it to Auto Configure by Name.

This will give you all original rows, whether or not they matched.

2. Add a Formula Tool after the Union.

---

In the Formula Tool:

Create a new column like FinalResult with logic:

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

> ToNumber() helps avoid null comparison errors.

---

Final Alteryx Workflow:

1. Input Data

2. Record ID

3. Filter D = "Yes"/"No"

4. Join original with filtered (O=O+ Right_RecordID = Left_RecordID)

5. Summarize → CountYesNo by Left_RecordID

6. Join summarized count back to original using RecordID

7. Union J + L outputs (to retain all original rows)

8. Formula Tool (to apply Excel-like logic)

9. Output Data