COUNTIF Dynamic Range (replicating Excel Formula to Alteryx)

[Karlo]

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 B) 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.

[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 · Accepted solution]

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

[Karlo]

@jasperlch thanks for the assist.

I actually found a solution via my formula (just had to slightly tweak it)

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

and grouped by the column itself with the Multi-row formula tool.

Although I checked what you suggested and yes this definitely works too (nice and easy fix to a complex problem and I was going about it the long way - I will try and keep both solutions in my workflow in containers for future reference.)

Thanks again :)

[jasperlch]

No problem. But i believe you could simplify your workflow even more. first you do not need to create a new Count column before the multi row formula tool. you could create the Count column inside the multi row formula instead. then yes, group by TPI. since you have already grouped by TPI, the [Row-1:TPI] is always gonna be the same as [TPI]. So, you do not need the if then else statement in your formula. All you needa put in the multi row formula is [Row-1:Count] + 1. Hope this helps.

[Karlo]

@jasperlch thanks I am not too sure how i can create a count variable in the multi-row formula. Apart from that the solution you provided I tried it but I dont get the same result as the tile tool that you mentioned or what I have done with the workflow now. Its missing some information to be honest. Maybe I am not doing it right though...

[jasperlch]

Hi @Karlo, try this: