COUNTIFS

[timol]

Dear all

I have the following data structure

Company	Year1	Year2
A	2018	2015
A	2018	2015
A	2018	2016
A	2018	2016
A	2018	2016
A	2018	2018
A	2018	2018
A	2018	2018
A	2018	2018

Now I want Alteryx to give out a count for each year 2015-2020

But it should only count of Year 2 os lower of equal to Year1

So that would be the output

Company	Year	Count
A	2015	0
A	2016	0
A	2017	0
A	2018	9

Can anyone help me doing this in Alteryx?

[timol]

Thank you so much for all your help!!!!

But isn't it the case that the condition is true for the first 11 rows?

[afv2688]

Hello @timol ,

The condition is not true for the first 11 rows. Row 10 and 11 have both year1 = 2017 and year2 2017 and 2018.

Since you stipulated that year2 has to be equal or lower it cannot be the case for row 11.

Company	Year1	Year2	Founding
A	2018	2015	2013
A	2018	2015	2013
A	2018	2016	2013
A	2018	2016	2013
A	2018	2016	2013
A	2018	2018	2013
A	2018	2018	2013
A	2018	2018	2013
A	2018	2018	2013
A	2017	2017	2013
A	2017	2018	2013
A	2019	2015	2013
A	2019	2017	2013
A	2019	2019	2013
A	2019	2012	2013
A	2019	2011	2013
A	2019	2007	2013
B	2016	2011	2015
B	2016	2011	2015
B	2016	2015	2015
B	2016	2015	2015

Regards

[timol]

You are absolutely right

Sorry for the confusion!

The output I would like to generate looks like this:

Company	Year	Count
A	2013	0
A	2014	0
A	2015	0
A	2016	0
A	2017	1
A	2018	11
B	2015	0
B	2016	4
B	2017	4
B	2018	4

I created the following excel sheet:

In Cell H2 you can see the formula I used:

=+COUNTIFS($A$1:$A$22;F2;$B$1:$B$22;"<="&G2;$C$1:$C$22;"<="&G2)

Again the minimum Year should be the Founding year and the maximum year should be 2020.

That is what I actually want to rebuild in Alteryx. Is there any way to do this?

Thank you so much for your help!

[afv2688 · Accepted solution]

Here you go,

max year had to be a manual input since there is no data associated to it.

Regards