Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.

Counting Across Columns

7 - Meteor

Hi all,


I'm trying to find a dynamic way to count the number of specific values across columns. Here's a sample of the data:


StockClient 1Client 2Client 3Client 4


I'd like to create a new column with a count of the instances of 0. I don't want to use the formula tool because my column headers have account numbers rather than Client 1, Client 2, etc and those account numbers will change regularly. I think the multi-row formula or multi-field formula might help but I haven't been able to get those to work.


Thank you all very much in advance!!

14 - Magnetar
14 - Magnetar

Hey @cmohyi 


I have attached a workflow that should get you the result you're looking for. I added a couple rows and changed the data a little bit to try and account for different circumstances.


First transpose your data, use a Summarize tool to count the instance of each value for each stock for each name. Use the Filter to only keep the 0 values. Use a Cross Tab to get the data oriented correctly again. Lastly use a Join then a Union to get your final output.


Hope this helps!



Alteryx Certified Partner
Alteryx Certified Partner

Hi @cmohyi 


The solution is very simple:




- The secret when you want to dynamic Summarize something is to transpose your data.


- Transpose data (Key Field should be Stock, I assume) - (Data Fields - Check Dynamic/Unknown Fields)

- Create a Flag to indicate if your Value field is 0 (Flag 1 or 0)

- Group by Stock and Sum the zeroes

- Join it back to the dataset


WF appended.