Hello All, I am trying to do a simple countif in Alteryx but it looks like it's not straightforward. I just wanted to count the currencies per Account unit. Please see below sample. Thanks for the help. Source: Account unitCurrencyUNIT 113EURUNIT 113EURUNIT 113EURUNIT 113USDUNIT 113USDUNIT 112EURUNIT 112EURUNIT 112USDUNIT 112USDUNIT 112USD Output: Row LabelsA AccountB AccountGrand TotalItem CountEUR-165522.27192850.6727328.4 unit 11331652.24 31652.243unit 112-65.64-0.01-65.652USD-165522.27192850.6727328.4 unit 11331652.24 31652.242unit 112-65.64-0.01-65.653

Countif function

Hello All,

I am trying to do a simple countif in Alteryx but it looks like it's not straightforward.

I just wanted to count the currencies per Account unit. Please see below sample. Thanks for the help.

Source:

Account unit	Currency
UNIT 113	EUR
UNIT 113	EUR
UNIT 113	EUR
UNIT 113	USD
UNIT 113	USD
UNIT 112	EUR
UNIT 112	EUR
UNIT 112	USD
UNIT 112	USD
UNIT 112	USD

Output:

Row Labels	A Account	B Account	Grand Total	Item Count
EUR	-165522.27	192850.67	27328.4
unit 113	31652.24		31652.24	3
unit 112	-65.64	-0.01	-65.65	2
USD	-165522.27	192850.67	27328.4
unit 113	31652.24		31652.24	2
unit 112	-65.64	-0.01	-65.65	3

Accepted answers

jdminton

This should work for you. Unfortunately, it does take more steps to do this in Alteryx.

Countif function.yxmd

geraldo

@Jenina

An example workflow of how to get the result you want

Countif.yxmd

All comments

geraldo

@Jenina

use the summarize tool group by Account unit Currency and count Currency

Jenina

@geraldo sorry, forgot to mention I also need to consider the A account and B account criteria, I need the total count of currency per account unti existing in both A and B accounts. That's where I am having issues.

apathetichell

You can do that all in the summarize tool. group by both fields. count of either fields. sum of your sum columns.

Jenina

@apathetichell do you have a sample workflow? source below. What I am getting is the total per GL account, when I tried to sum it is not getting the correct count.

Account unit	Currency	GL Account
UNIT 113	EUR	A Account
UNIT 113	EUR	B Account
UNIT 113	EUR	B Account
UNIT 113	USD	A Account
UNIT 113	USD	B Account
UNIT 112	EUR	A Account
UNIT 112	EUR	B Account
UNIT 112	USD	A Account
UNIT 112	USD	B Account
UNIT 112	USD	A Account

apathetichell

post some sample data -and I can mock something up. - this sounds like you need to group by an additional field. - you will need to do two different breakdowns and some kind of join. Having two different sub total types (by currency and by account - at least in the format you have) would require some pivots/joining.

Jenina

@apathetichell I attached a sample data. Thanks!

countif.xlsx

jdminton

This should work for you. Unfortunately, it does take more steps to do this in Alteryx.

Countif function.yxmd

Jenina

@jdminton I am trying this now, could you pls send me the input you used, thank you!

jdminton

@Jenina I used the one you provided I did start the data on line 2 though in the configuration.

geraldo

@Jenina

An example workflow of how to get the result you want

Countif.yxmd

Jenina

@jdminton this works! and yes it's complicated. Thanks!

Jenina

@geraldo this is amazing, thank you!

jdminton

I’m glad it works for you. You can do something similar with reporting tools but I find those less useful if you’re going to use the same data downstream.

Jenina

@geraldo going back to this, If I want to add an Inventory column, where should I place in the 3 summarize tools that will still result to the same output we have.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703