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

Question

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.

CompanyRegionCOUNTIFSDivisionStageTypeCategoryDatePrimary OP ContactInitial Engagement ContactAdditional Information and CommentsXYZ1EMEA1UKIxxxxxxxxxxxx xxx xxx xxxXYZ2NORAM1US Centralxxxxxxxxxxxx xxx xxxxxxXYZ3NORAM1US Westxxxxxxxxxxxx xxx xxx xxxXYZ4NORAM1US Eastxxxxxxxxx xxx xxx xxx xxxXYZ5APAC1Indiaxxxxxxxxx xxx xxx xxxxxxXYZ6NORAM2US Eastxxxxxxxxx xxx xxx xxx xxxXYZ6NORAM2US Eastxxxxxxxxx xxx xxx xxx xxxXYZ7EMEA2UKIxxxxxxxxx xxx xxx xxx xxxXYZ7EMEA2UKIxxxxxxxxx xxx xxx xxx xxx

COUNTIFs.yxmd

icchung · Answer

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

apathetichell · Answer

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