SUMIFS Formula

[timol]

Dear all,

I have the follwing type of data structure and would like to do a SUMIFS formula in Alteryx

Company	Employees	Year	Founding
A	3	2012	2013
A	4	2013	2013
A	5	2013	2013
A	23	2014	2013
A	535	2014	2013
A	2018	2019	2013
A	424	2020	2013
A	43	2020	2013
A	13	2017	2013
A	24	2018	2013
A	4	2014	2013
A	6	2014	2013
A	7	2019	2013
A	4	2020	2013
A	45	2020	2013
A	34	2017	2013
A	676	2018	2013
B	454	2019	2015
B	86	2018	2015
B	46	2017	2015
B	53	2017	2015

I want to SUMIF (accumulate) the number of employees for every year

I want to show the timespan from Founding year until 2018

so e.g. for 2017 I want to sum up all values <= 2017

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

Can anyone help me implementing this in Alteryx?

[AngelosPachis]

Hi @timol ,

I'm not sure I fully understand the question, but hopefully I do. I have summed the values up if the [Year] is less than or equal to the [Founding Year] for each company and done the same for those years that are greater than the founding year. Then I union everything back.

Hope that helps, let me know if that's what you were looking for please (A desired output table always helps)

Cheers,

Angelos

[timol]

Thank you!

I changed the input data a little bit since it made no sense (Employees before founding):

Company	Employees	Year	Founding
A	3	2013	2013
A	4	2013	2013
A	5	2013	2013
A	23	2014	2013
A	535	2014	2013
A	2018	2019	2013
A	424	2020	2013
A	43	2020	2013
A	13	2017	2013
A	24	2018	2013
A	4	2014	2013
A	6	2014	2013
A	7	2019	2013
A	4	2020	2013
A	45	2020	2013
A	34	2017	2013
A	676	2018	2013
B	454	2019	2015
B	86	2018	2015
B	46	2017	2015
B	53	2017	2015

Please see a screenshot of the output and formula in excel below

Thank you!

[afv2688]

Hello @timol ,

Here you go:

Regards

[timol]

Thank you!!

Company	Employees	Year	Founding
A	3	2012	2013
A	4	2013	2013
A	5	2013	2013
A	23	2014	2013
A	535	2014	2013
A	2018	2019	2013
A	424	2020	2013
A	43	2020	2013
A	13	2017	2013
A	24	2018	2013
A	4	2014	2013
A	6	2014	2013
A	7	2019	2013
A	4	2020	2013
A	45	2020	2013
A	34	2017	2013
A	676	2018	2013
B	454	2019	2015
B	86	2018	2015
B	46	2017	2015
B	53	2017	2015

How could I change the solution if I would have some data points where Year > Founding that I would still like to include?

So I would still like to count them but only show the time period "Founding" until 2018

[afv2688]

Hello @timol ,

You can add a filter to the end of the workflow to select the years

Regards

[timol]

Thanks!

But this solution would still not count the first data point right?

How could I change the solution if I would have some data points where Year > Founding that I would still like to include?

So the SUMIF formula should work regardless of the founding year but in the end only year equal or after the founding year should be displayed

[afv2688]

Hello @timol ,

Could you please explain it with an example? I don't understand what you mean.

Regards

[timol]

Assume the following data structure (slightly changed compared to the one before)

Company	Employees	Year	Founding
A	3	2012	2013
A	4	2013	2013
A	5	2013	2013
A	23	2014	2013
A	535	2014	2013
A	2018	2019	2013
A	424	2020	2013
A	43	2020	2013
A	13	2017	2013
A	24	2018	2013
A	4	2014	2013
A	6	2014	2013
A	7	2019	2013
A	4	2020	2013
A	45	2020	2013
A	34	2017	2013
A	676	2018	2013
B	454	2019	2015
B	86	2018	2015
B	46	2017	2015
B	53	2017	2015

This should be the output

[afv2688 · Accepted solution]

Hello @timol ,

how about now?

Regards