How to create a countifs base on two or more criterias(columns)

[icchung]

Hi all,

I appreciate any help or suggestions to this Alteryx workflow.

In the my excel I have COUNTIFS COLUMN [=COUNTIFS(A:A,A2,B:B,B2)] to identify the same Company & Region. Is this possible to do in Alteryx. The ERROR in my current workflow it's duplicating the rows when I join back the details "xxx" from the count "E" node.

Company	Region	COUNTIFS	Division	Stage	Type	Category	Date	Primary OP Contact	Initial Engagement Contact	Additional Information and Comments
XYZ1	EMEA	1	UKI	xxx	xxx	xxx	xxx	xxx	xxx	xxx
XYZ2	NORAM	1	US Central	xxx	xxx	xxx	xxx	xxx	xxx	xxx
XYZ3	NORAM	1	US West	xxx	xxx	xxx	xxx	xxx	xxx	xxx
XYZ4	NORAM	1	US East	xxx	xxx	xxx	xxx	xxx	xxx	xxx
XYZ5	APAC	1	India	xxx	xxx	xxx	xxx	xxx	xxx	xxx
XYZ6	NORAM	2	US East	xxx	xxx	xxx	xxx	xxx	xxx	xxx
XYZ6	NORAM	2	US East	xxx	xxx	xxx	xxx	xxx	xxx	xxx
XYZ7	EMEA	2	UKI	xxx	xxx	xxx	xxx	xxx	xxx	xxx
XYZ7	EMEA	2	UKI	xxx	xxx	xxx	xxx	xxx	xxx	xxx

[apathetichell]

yes. It's actually quite easy. You just use group by two categories and a count field. Having said that your join configuration is off. You should really be using a regular join - not a join multiple. and when you are joining a non-unique identifier in one of your join sets it will join n times where n is the times the non-unique identifier occurs for each non-unique identifier. In your case - Alteryx has no idea which Johnson & Johnson should join to which Johnson & Johnson - so it joins them all. You can fix this by joining on multiple columns...

[icchung]

Thanks for helping out.

It is showing me duplicated rows when there should be only 6 rows.

It should be showing the record that has a different status (highlighted)

in the excel the countifs is only base on Company and region but it doesn't see the case with alteryx countifs