Countifs in Alteryx

[concel]

Hello,

I have below table

Company 1	1
Company 2	0
Company 1	2
Company 3	3
Company 1	4
Company 3	1
..	..

I like to display in Alteryx in below format with one record for each company. In excel I can use countifs,  =COUNTIFS(A:A,C2,B:B,4) for each column

How can I achieve the similar in Alteryx. Any help is appreciated.

	Count of 1	Count of 2	Count of 3	Count of 4
Company 1	x	x	x	x
Company 2	x	x	x	x
Company 3	x	x	x	x
…	…	…	…	…

[flying008]

Hi, @concel 

Maybe this is your want ?

Input				Output
Name	Value			Name	Concat_0	Concat_1	Concat_2	Concat_3	Concat_4
Company 1	1			Company 1		1	1		1
Company 2	0			Company 2	1				2
Company 1	2			Company 3		1	1	1
Company 3	3
Company 1	4
Company 3	1
Company 3	2
Company 2	4
Company 2	4

Multi-Field Formula:

iif(isempty(trim([_CurrentField_],",")) ,Null(),CountWords(trim([_CurrentField_],","))-1)

*******

If it can help you, please mark it as a solution for help share more.

[concel]

Hi @flying008,

Not sure how I can set the individual tools to work as you show.

And I have thousands of companies to run this for.

Not sure how I can use your method for this many companies.

[concel]

Here is the input and the output I desire.

[Emmanuel_G · Accepted solution]

Hi @concel ,

Find attached the way to do this easily.

Let me know if there is any issue and do not hesitate to mark as solution if it helped.

[flying008 · Accepted solution]

Hi, @concel 

As your input data from xlsx, like this flow:

***********

Finally, I optimized the original process with @Emmanuel_G  so that the entire workflow can be fully automated. No matter the number and name of your field columns change, there is no need to manually adjust or write formulas.

[concel]

Hi @Emmanuel_G , @flying008 

Thank you very much!

I was able to find a solution. But your suggested solution is much neater.

I appreciate it!