Equivalent to Excel COUNTIF() function

[tom_montpool]

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.

[jack_morgan]

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

[jdunkerley79]

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.

[tom_montpool]

In Excel, what I'm doing is a row-wise COUNTIF -- see the image below:

I've attached a workflow that has two different approaches in Alteryx.

[jdunkerley79]

I was going to suggest the transpose solution, think that is the easiest way to do it.

[BCarley]

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

[jb]

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!

[JohnJPS]

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.

[JBO]

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?

[JohnJPS]

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.