COUNTIF Dynamic Range (replicating Excel Formula to Alteryx)

I am in a bit of a standstill at the moment as I am unable to get this countif to work on Alteryx.

The sample provided is a small subsection of the data but there are more columns etc that I need to associate. I thought by using a summarise tool grouping the "TPI" column and doing a count on it I would be able to join it back to the data and get a count of that column but unfortunately this does not work as numbers on Excel don't match the numbers on Alteryx.

The main reason being is the group function I think as once this is done only one value shows

I have provided the sample data (on excel and formula to try and convert into an alteryx format is present in column attached, any help on this would be hugely appreciated. One thing to note is within column B each cell the formula differs slightly as the range changes.

Many thanks in advance.

Sample test.xlsx

Datasets

Reporting

Tips and Tricks

Parse

Best Practices

Join

Preparation

Data Investigation

Accepted answers

jasperlch

Hi, i think the Tile tool does exactly what you are asking for without the need of formula:

New Workflow1.yxmd

All comments

Karlo

I think I have found a way myself...with the Multi-row formula tool.

I am close but I am still not getting the right numbers.

I created a column called count and equated it to 1.

Then using the multi-row formula have created this.

IF [Tax Pack Issued (Text)]!=[Row-1:Tax Pack Issued (Text)]
THEN [Count]
ELSEIF [Tax Pack Issued (Text)]=[Row-1:Tax Pack Issued (Text)]
THEN [Count]+1
ELSEIF [Tax Pack Issued (Text)]=[Row-1:Tax Pack Issued (Text)] AND [Row-2:Tax Pack Issued (Text)]=[Tax Pack Issued (Text)]
THEN [Count]+2
ELSE [Count]

The reason for my last elseif is that if the row 1 = row 2 = row 3 then row 3 should count as 3; row 2 should have a count as 2; and row 1 should have a count as 1.

I hope I am making sense but I still am not getting the right figures for my data.

Any help would be hugely appreciated.

Thanks

jasperlch

Hi, i think the Tile tool does exactly what you are asking for without the need of formula:

New Workflow1.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703