How can I perform the sum function in columns and rows of Excel in Alteryx?

[albertfung]

How can I perform the sum function in columns and rows of Excel in Alteryx?

[alexnajm]

I am glad this was posted separately! Sums by rows = Formula, Sums by column = Summarize

But if you want further help, you'll need to provide more information

[albertfung]

Thanks for your prompt reply. I combine 2 very big Excel files 11M and 15M and show you part of the files below.

Left Account ID	Age of debtor	Business Segment	Termination reason - standardized	Current Charges Outstanding	Current Interest Outstanding	Current Principal Outstanding	Date Closed	Date into collection	Date of Legal Title	Interest rate (%)	Is Closed	portfolio_batch_id	Principal into Collection/Registered	Purchase_Date (EOM)	Summary Case ID	Total Balance Outstanding	Verdict exists	Right_2004_01_01_00_00_00	Right_2004_02_01_00_00_00	Right_2004_03_01_00_00_00	Right_2004_04_01_00_00_00	Right_2004_05_01_00_00_00	Right_2004_06_01_00_00_00	Right_2004_07_01_00_00_00	Right_2004_08_01_00_00_00	Right_2004_09_01_00_00_00	Right_2004_10_01_00_00_00	Right_2004_11_01_00_00_00	Right_2004_12_01_00_00_00	Right_2005_01_01_00_00_00	Right_2005_02_01_00_00_00	Right_2005_03_01_00_00_00	Right_2005_04_01_00_00_00	Right_2005_05_01_00_00_00	Right_2005_06_01_00_00_00	Right_2005_07_01_00_00_00	Right_2005_08_01_00_00_00	Right_2005_09_01_00_00_00	Right_2005_10_01_00_00_00	Right_2005_11_01_00_00_00	Right_2005_12_01_00_00_00	Right_2006_01_01_00_00_00	Right_2006_02_01_00_00_00	Right_2006_03_01_00_00_00	Right_2006_04_01_00_00_00	Right_2006_05_01_00_00_00	Right_2006_06_01_00_00_00	Right_2006_07_01_00_00_00	Right_2006_08_01_00_00_00	Right_2006_09_01_00_00_00	Right_2006_10_01_00_00_00	Right_2006_11_01_00_00_00	Right_2006_12_01_00_00_00	Right_2007_01_01_00_00_00	Right_2007_02_01_00_00_00	Right_2007_03_01_00_00_00	Right_2007_04_01_00_00_00	Right_2007_05_01_00_00_00	Right_2007_06_01_00_00_00	Right_2007_07_01_00_00_00	Right_2007_08_01_00_00_00	Right_2007_09_01_00_00_00	Right_2007_10_01_00_00_00	Right_2007_11_01_00_00_00	Right_2007_12_01_00_00_00	Right_2008_01_01_00_00_00	Right_2008_02_01_00_00_00	Right_2008_03_01_00_00_00	Right_2008_04_01_00_00_00	Right_2008_05_01_00_00_00	Right_2008_06_01_00_00_00	Right_2008_07_01_00_00_00	Right_2008_08_01_00_00_00	Right_2008_09_01_00_00_00	Right_2008_10_01_00_00_00	Right_2008_11_01_00_00_00	Right_2008_12_01_00_00_00	Right_2009_01_01_00_00_00	Right_2009_02_01_00_00_00	Right_2009_03_01_00_00_00	Right_2009_04_01_00_00_00	Right_2009_05_01_00_00_00	Right_2009_06_01_00_00_00	Right_2009_07_01_00_00_00	Right_2009_08_01_00_00_00	Right_2009_09_01_00_00_00	Right_2009_10_01_00_00_00	Right_2009_11_01_00_00_00	Right_2009_12_01_00_00_00	Right_2010_01_01_00_00_00	Right_2010_02_01_00_00_00	Right_2010_03_01_00_00_00	Right_2010_04_01_00_00_00	Right_2010_05_01_00_00_00	Right_2010_06_01_00_00_00	Right_2010_07_01_00_00_00	Right_2010_08_01_00_00_00	Right_2010_09_01_00_00_00	Right_2010_10_01_00_00_00	Right_2010_11_01_00_00_00	Right_2010_12_01_00_00_00	Right_2011_01_01_00_00_00	Right_2011_02_01_00_00_00	Right_2011_03_01_00_00_00	Right_2011_04_01_00_00_00	Right_2011_05_01_00_00_00	Right_2011_06_01_00_00_00	Right_2011_07_01_00_00_00	Right_2011_08_01_00_00_00	Right_2011_09_01_00_00_00	Right_2011_10_01_00_00_00	Right_2011_11_01_00_00_00	Right_2011_12_01_00_00_00	Right_2012_01_01_00_00_00	Right_2012_02_01_00_00_00	Right_2012_03_01_00_00_00	Right_2012_04_01_00_00_00	Right_2012_05_01_00_00_00	Right_2012_06_01_00_00_00	Right_2012_07_01_00_00_00	Right_2012_08_01_00_00_00	Right_2012_09_01_00_00_00	Right_2012_10_01_00_00_00	Right_2012_11_01_00_00_00	Right_2012_12_01_00_00_00	Right_2013_01_01_00_00_00	Right_2013_02_01_00_00_00	Right_2013_03_01_00_00_00	Right_2013_04_01_00_00_00	Right_2013_05_01_00_00_00	Right_2013_06_01_00_00_00	Right_2013_07_01_00_00_00	Right_2013_08_01_00_00_00	Right_2013_09_01_00_00_00	Right_2013_10_01_00_00_00	Right_2013_11_01_00_00_00	Right_2013_12_01_00_00_00	Right_2014_01_01_00_00_00	Right_2014_02_01_00_00_00	Right_2014_03_01_00_00_00	Right_2014_04_01_00_00_00	Right_2014_05_01_00_00_00	Right_2014_06_01_00_00_00	Right_2014_07_01_00_00_00	Right_2014_08_01_00_00_00	Right_2014_09_01_00_00_00	Right_2014_10_01_00_00_00	Right_2014_11_01_00_00_00	Right_2014_12_01_00_00_00	Right_2015_01_01_00_00_00	Right_2015_02_01_00_00_00	Right_2015_03_01_00_00_00	Right_2015_04_01_00_00_00	Right_2015_05_01_00_00_00	Right_2015_06_01_00_00_00	Right_2015_07_01_00_00_00	Right_2015_08_01_00_00_00	Right_2015_09_01_00_00_00	Right_2015_10_01_00_00_00	Right_2015_11_01_00_00_00	Right_2015_12_01_00_00_00	Right_2016_01_01_00_00_00	Right_2016_02_01_00_00_00	Right_2016_03_01_00_00_00	Right_2016_04_01_00_00_00	Right_2016_05_01_00_00_00	Right_2016_06_01_00_00_00	Right_2016_07_01_00_00_00	Right_2016_08_01_00_00_00	Right_2016_09_01_00_00_00	Right_2016_10_01_00_00_00	Right_2016_11_01_00_00_00	Right_2016_12_01_00_00_00	Right_2017_01_01_00_00_00	Right_2017_02_01_00_00_00	Right_2017_03_01_00_00_00	Right_2017_04_01_00_00_00	Right_2017_05_01_00_00_00	Right_2017_06_01_00_00_00	Right_2017_07_01_00_00_00	Right_2017_08_01_00_00_00	Right_2017_09_01_00_00_00	Right_2017_10_01_00_00_00	Right_2017_11_01_00_00_00	Right_2017_12_01_00_00_00	Right_2018_01_01_00_00_00	Right_2018_02_01_00_00_00	Right_2018_03_01_00_00_00	Right_2018_04_01_00_00_00	Right_2018_05_01_00_00_00	Right_2018_06_01_00_00_00	Right_2018_07_01_00_00_00	Right_2018_08_01_00_00_00	Right_2018_09_01_00_00_00	Right_2018_10_01_00_00_00	Right_2018_11_01_00_00_00	Right_2018_12_01_00_00_00	Right_2019_01_01_00_00_00	Right_2019_02_01_00_00_00	Right_2019_03_01_00_00_00	Right_2019_04_01_00_00_00	Right_2019_05_01_00_00_00	Right_2019_06_01_00_00_00	Right_2019_07_01_00_00_00	Right_2019_08_01_00_00_00	Right_2019_09_01_00_00_00	Right_2019_10_01_00_00_00	Right_2019_11_01_00_00_00	Right_2019_12_01_00_00_00	Right_2020_01_01_00_00_00	Right_2020_02_01_00_00_00	Right_2020_03_01_00_00_00	Right_2020_04_01_00_00_00	Right_2020_05_01_00_00_00	Right_2020_06_01_00_00_00	Right_2020_07_01_00_00_00	Right_2020_08_01_00_00_00	Right_2020_09_01_00_00_00	Right_2020_10_01_00_00_00	Right_2020_11_01_00_00_00	Right_2020_12_01_00_00_00	Right_2021_01_01_00_00_00	Right_2021_02_01_00_00_00	Right_2021_03_01_00_00_00	Right_2021_04_01_00_00_00	Right_2021_05_01_00_00_00	Right_2021_06_01_00_00_00	Right_2021_07_01_00_00_00	Right_2021_08_01_00_00_00	Right_2021_09_01_00_00_00	Right_2021_10_01_00_00_00	Right_2021_11_01_00_00_00	Right_2021_12_01_00_00_00	Right_2022_01_01_00_00_00	Right_2022_02_01_00_00_00	Right_2022_03_01_00_00_00	Right_2022_04_01_00_00_00	Right_2022_05_01_00_00_00	Right_2022_06_01_00_00_00	Right_2022_07_01_00_00_00	Right_2022_08_01_00_00_00	Right_2022_09_01_00_00_00	Right_2022_10_01_00_00_00	Right_2022_11_01_00_00_00	Right_Account ID
455964	78	Bank	NULL	0	14577.45	29494.49		05/12/2008 00:00	2009-01-22	15.94	0	5843	29494.49	31/01/2015 00:00	0	44071.94	1																																																																																																		107	355	356	338	697	339	364	326	320	309			300	301	286	262	1215	273	273	264	276	276			290	331	316	315	1346	328	341	345	377	347			238	388	367	366	1564	368	400	367	365	366		140	345	357	359	347	1554	370	369	395	352	328			349	371	355	372	1534	368	461	363	343	344			289	345	329	329	1586	370	529	369	343	343	345	337			1431	348	371	346	500	482	319	2276	369				984	379	380	381	392	381	364	365	366	378	360		134	381	1956	380	391	379	362	363	0	378	341			358	1874	364	375	364	346	347	455964
424303	50	Bank	NULL	600	86364.85	48105.76		06/11/2009 00:00	2009-12-16	14.49	0	12810	48105.76	31/12/2018 00:00	0	135070.6	1																																																																										349			532	1924	773											687																										194	575	575	409										166																																																																																																			424303
145957	NULL	Bank	Fully paid/partly paid	0	0	0	2013-08-21	25/08/2004 00:00	2002-03-20	17.8	1	2596	4829.3	31/05/2010 00:00	0	0	0																																																																																																1200																				8475																																																																																																																145957
74453	NULL	Bank	Fully paid/partly paid	0	0	0	2019-09-16	04/03/2006 00:00	1996-12-20	9.46	1	2399	4567.5	31/08/2004 00:00	0	0	0																																																																			4567.5																																																																		669	1266	1264	1262		1252	1249	3929		2485	1239	650		1247	1242		2462	6239	382			1135			921		360	376		3865		696							102	319		1282			1533			903	4374		236		562	626	528	262	5608.13																																							74453
15540	46	Bank	NULL	600	1916.708	7730.5		04/03/2006 00:00	2004-01-21	7.52	0	2399	10399.94	31/08/2004 00:00	0	10247.21	1																																2044																																																																																																																									3635									292		865		923	879	879	882	878	888			608	386	394	850										977	463																																								15540
5357695	47	Retail	NULL	1460	717.9363	0		10/07/2017 00:00	2017-08-15	24	0	11323	756.1	28/02/2018 00:00	0	2177.936	1																																																																																																																																																																																																										263												266												267		5357695
3268418	51	Retail	NULL	1438	9254.294	5508		22/09/2004 00:00	2016-06-20	8	0	5838	5940	31/01/2015 00:00	0	16200.29	1																																																																																																																																																																																																																	216												216							3268418
4532228	42	Retail	Fully paid/partly paid	0	0	0	2017-07-03	31/10/2016 00:00	2017-01-04	24	1	10597	318	30/04/2017 00:00	0	0	0																																																																																																																																																																200	315	774																																																																		4532228
429797	59	Bank	NULL	1196	38742.2	19260.39		07/11/2011 00:00	2012-01-02	16.24	0	12810	21670.39	31/12/2018 00:00	0	59198.59	1																																																																																																																																																																						204																																												2410																		429797
2628217	56	Bank	NULL	600	5970.514	9561.51		08/04/2014 00:00	2014-08-18	16.24	0	12810	9561.51	31/12/2018 00:00	0	16132.02	1																																																																																																																																											455				372																																																																									2821		2103	2083				582					2628217
161055	38	Bank	NULL	600	21590.67	17298.6		06/11/2008 00:00	2009-09-16	18.25	0	2596	20895.6	31/05/2010 00:00	0	39489.27	1																																																																																373	352																				1296	278	2540	5130		3964	4251		932	246	430		259	316	1180			634	1002		469									376								202						616	300							570		691		409	268		642	569	148	1883																																																																		161055

The original combined file is 29M with 1411128 row. The file contains information about non performing loan (NPL). I want to add up from Right_2004_01_01_00_00_00 to Right_2022_11_01_00_00_00 to calculate the total repayment of each account ID. In addition, I want to answer the following 3 questions. Would you please tell me a quick way to do it? Thank.

1. Stratification of the portfolio
2. Analysis of the underlying book
3. Segmentation of the book and the reasons behind it

[alexnajm]

Transpose and Crosstab will be your fastest way for dynamic aggregation - I think you may need to go through some of the interactive lessons and the learning path to learn Alteryx as it seems like this is a new tool to you!

[alexnajm]

The other questions are beyond the scope of typical community questions since they require industry expertise, so hopefully someone can help

[albertfung]

I also guess Transpose and Crosstab can work for it. However, I think there may have other quicker ways to do it, as it is just a simple function in Excel. Thanks for your advices.

[alexnajm]

You can use the same SUM function in Alteryx in a Formula tool, but just like Excel it won't be dynamic when new columns come in to sum - Transpose and Crosstab would be your best option for dynamic aggregation.

If you don't want dynamic processes, then use a SUM if you are summing a row, or a Summarize if you are summing a column