topic Replicate excel formula IF(COUNTIF) in Alteryx in Alteryx Designer Desktop Discussions

Replicate excel formula IF(COUNTIF) in Alteryx

aparna0208 — Tue, 06 Aug 2019 19:23:27 GMT

Hi,

Is there a way to replicate below excel formula in Alteryx? I tried my best to use the filter and formula tool by using IF and CONTAINS function but nothing worked. Any help would be appreciated. Thank you in advance!

IF(COUNTIF(A$2:A$6,A2)>1,TRUE,FALSE)

Re: Replicate excel formula IF(COUNTIF) in Alteryx

JosephSerpis — Tue, 06 Aug 2019 19:25:41 GMT

Hi @aparna0208 sounds like you want to use a summazie tool then a formula tool to do an If to flag if its true or false. yOU Could just use a filter after the summaize tool as well.

Re: Replicate excel formula IF(COUNTIF) in Alteryx

Thableaus — Tue, 06 Aug 2019 19:29:09 GMT

Hi @aparna0208

The way you approach certain situations in Alteryx require a different mindset you're used to in Excel.

Here's what I'd do:

Use Summarize Tool to count the occurences of this cell, grouping by your A field;

Use IF Function to define TRUE or FALSE if that cell in specific has a count greater than 1;

Join the result back to your dataset by your A field if you need to repeat this value.

Cheers,

Re: Replicate excel formula IF(COUNTIF) in Alteryx

jdunkerley79 — Fri, 05 May 2023 09:41:55 GMT

I would suggest:

The summarise creates the counts

Then a formula tool to turn to True/False

Finally join back to the original data

One small catch is the data is reordered in this process, should this be a problem then you can use a RecordID to know original order.

Sample attached

Re: Replicate excel formula IF(COUNTIF) in Alteryx

aparna0208 — Tue, 06 Aug 2019 20:46:42 GMT

It worked! Thank you so much @JosephSerpis @Thableaus @jdunkerley79 for your inputs on this:)