This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Hi,
A colleague came to me to ask if Alteryx has an equivalent to the COUNTIF() function in Excel.
Basically, the goal is to look across fields in a single row and create a new field containing a count of cells that match a specified criterion.
The Excel syntax is:
My mind goes to two ideas:
1) Transpose the table (but this gets really big really fast)
2) Use MultiField formula IF statement to make booleans of the criteria and then a manually created Formula to add the new fields.
Neither of these is as simple as COUNTIF() so I was wondering if anyone else had any ideas.
Morning Tom!
I'm with you, I can't think of a basic built in function to do this, the best i can do is to use a formula and summaraize tool. I don't know if it'll help but take a look at the sample attachment. It essentially tags a new field based on contains t/f and then sums that column.
Jack
For a different way you could use a MultiRow formula creating a running CountIf:
[Row-1:CountIf]+IIF(MOD([RowCount],7)=0,1,0)
and then a Sample formula to pick last row
Attached a demo counting numbers divisible by 7 from 1 to 10000
Could be wrapped in a macro where you supply the IIF clause.
I was going to suggest the transpose solution, think that is the easiest way to do it.
Hello!
Good news, this question helped me solve a problem I was having at work today so I thought I would make a macro out of the solution and package it up.
https://gallery.alteryx.com/#!app/CountIf/56b2e20faa690a0ef83703c2
What I have done is create an iterative macro that moves through each field defined in the range, by a checkbox, and checks whether the provided expression is True or not for each row. As the rows will have variable names I have specified that the user should use __field__ as their variable. i.e. __field__ = 1. I think that it should be relatively quick as you don't have to transpose anything but mainly I think it is a flexible solution that easily accepts changes in workflows.
I will concede that I spent too much time on it, if anyone has a better way of doing the iterative column selection I would be interested to hear it. Also if someone can tell me why I had to use iteration_number+ 2 selection, that would also be helpful. I think it is to do with my count field moving position but I cannot tell why that would happen.
Anyway, hope you get some use out of it too!
Ben - BIPB
Hi all,
Wondering if any more condensed solutions have been found to this question. In my case, I'm looking to filter on values that are present in a separate file. I've summarized the value (which in my example is called "Placement") in the ref sheet, and now want to filter a separate input file on Placement by saying, "only give me placements which are present in the ref sheet." Any thoughts?
Thank you!
Hi jb,
If you join the two files together, the inner join will contain rows present in both files; that should suffice as a lookup.
I always use Countif in Excel. Would be really nice to not have to do a bunch of workarounds in Alteryx for this basic function. Is it very hard to code this function in Alteryx?
Just FYI, if your Alteryx installation includes R (on the Developer tab), then it's pretty easy to do the same thing as an Excel CountIf... for instance:
df <- read.Alteryx("#1", mode="data.frame") df[,"countIf"] <- rowSums(df[,1:9]==0) write.Alteryx(df, 1)
Explanation: lines 1 and 3 simply pass data back and forth from/to Alteryx. So the "Count If" is just one line of code...
df[,"countIf"] <- rowSums(df[,1:9]==0)
...which is: adding a new column "countIf" to the dataset, which is assigned the row-wise sum over existing columns 1 to 9 where any of those columns is equal to zero. The sum is on the boolean "equality" which happens to be 1 when true and 0 when false; therefore you could supply any condition here.
I've attached the same, in a simple workflow.