community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
We will be upgrading the Gallery to our 2019.4 release this Saturday, December 7th beginning at 9:00am MT. We expect the outage to take last approx. 2.5 hours.

Equivalent to Excel COUNTIF() function

ACE Emeritus
ACE Emeritus

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:

Excel COUNTIF syntax description

 

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.

ACE Emeritus
ACE Emeritus

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.

ACE Emeritus
ACE Emeritus

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

Example.JPG

 

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

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

Alteryx Certified Partner

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

Meteor

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!

ACE Emeritus
ACE Emeritus

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.

 

Asteroid

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?

ACE Emeritus
ACE Emeritus

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.

Labels