SumIfs and condtional addition

[davidlocke]

I am replicating various pages of IRS Form 1120 in Alteryx for several entities.  I have already populated the individual data lines, but need to find a way to add the various subtotal and total lines on the form. Below is an example of where I am at presently:

1120 Pg 1 Line No	1120 Pg 1 Line Desc	TRC TCC Combo	1000	1100
1a	Gross Receipts or Sales	30-100.010	-781559441.4	-841483424.7
1b	Returns and Allowances	30-105.010	6166957.93	238364717.5
1c	Balance - Net Sales	null	0	0
2	Cost of Goods Sold	40-110.010	0	0
2	Cost of Goods Sold	40-120.010	545886855.8	477116716.3
2	Cost of Goods Sold	40-130.010	11202069.64	5071500.26
2	Cost of Goods Sold	40-140.010	0	0
2	Cost of Goods Sold	40-150.020	38.7	0
2	Cost of Goods Sold	40-150.025	-28592475	281139.12
2	Cost of Goods Sold	40-150.060	0	0
2	Cost of Goods Sold	40-150.100	0	0
2	Cost of Goods Sold	40-150.105	429.55	44.25
2	Cost of Goods Sold	40-150.180	2137809.66	967662.61
2	Cost of Goods Sold	40-150.220	276674.18	26157.04
2	Cost of Goods Sold	40-150.340	157650.72	343236.63
2	Cost of Goods Sold	40-150.460	0	0
2	Cost of Goods Sold	40-150.500	1488178.37	71143.72
2	Cost of Goods Sold	40-150.545	35164.98	32.83
2	Cost of Goods Sold	40-150.580	7124074.85	0
2	Cost of Goods Sold	40-150.585	1890203.63	2614223.2
2	Cost of Goods Sold	40-150.590	91116.46	0
2	Cost of Goods Sold	40-150.595	875354.25	0
2	Cost of Goods Sold	40-150.620	201251.21	39829.86
2	Cost of Goods Sold	40-150.625	133757.27	63900.28
2	Cost of Goods Sold	40-150.630	267930.36	36963.34
2	Cost of Goods Sold	40-150.635	208151.38	1035343.96
2	Cost of Goods Sold	40-150.700	46144.4	803413.85
2	Cost of Goods Sold	40-150.705	408316.4	0
2	Cost of Goods Sold	40-150.710	2404279.28	0
2	Cost of Goods Sold	40-150.740	0	0
2	Cost of Goods Sold	40-150.745	0	94466.08
2	Cost of Goods Sold	40-150.750	447391.1	8064.92
2	Cost of Goods Sold	40-150.755	608989.97	149742.72
2	Cost of Goods Sold	40-150.760	0	0
2	Cost of Goods Sold	40-150.780	823890.1	287858.02
2	Cost of Goods Sold	40-150.820	0	0
2	Cost of Goods Sold	40-150.900	7375759.52	0
2	Cost of Goods Sold	40-160.010	883629.17	348264.87
2	Cost of Goods Sold	40-170.010	38307.57	0
2	Cost of Goods Sold	40-190.010	0	0
3	Gross Profit	null	0	0

I need to sum lines 1a and 1b to create a subtotal that should display on 1c.  Then I need to  add line 1c with the sum all of the instances of line 2.  While there are other lines that I need to add, this should be enough data to help me replicate.

I would expect the solution to look like this:

1120 Pg 1 Line No	1120 Pg 1 Line Desc	TRC TCC Combo	1000	1100
1a	Gross Receipts or Sales	30-100.010	-781559441.4	-841483424.7
1b	Returns and Allowances	30-105.010	6166957.93	238364717.5
1c	Balance - Net Sales	null	-775392483.5	-603118707.2
2	Cost of Goods Sold	40-110.010	0	0
2	Cost of Goods Sold	40-120.010	545886855.8	477116716.3
2	Cost of Goods Sold	40-130.010	11202069.64	5071500.26
2	Cost of Goods Sold	40-140.010	0	0
2	Cost of Goods Sold	40-150.020	38.7	0
2	Cost of Goods Sold	40-150.025	-28592475	281139.12
2	Cost of Goods Sold	40-150.060	0	0
2	Cost of Goods Sold	40-150.100	0	0
2	Cost of Goods Sold	40-150.105	429.55	44.25
2	Cost of Goods Sold	40-150.180	2137809.66	967662.61
2	Cost of Goods Sold	40-150.220	276674.18	26157.04
2	Cost of Goods Sold	40-150.340	157650.72	343236.63
2	Cost of Goods Sold	40-150.460	0	0
2	Cost of Goods Sold	40-150.500	1488178.37	71143.72
2	Cost of Goods Sold	40-150.545	35164.98	32.83
2	Cost of Goods Sold	40-150.580	7124074.85	0
2	Cost of Goods Sold	40-150.585	1890203.63	2614223.2
2	Cost of Goods Sold	40-150.590	91116.46	0
2	Cost of Goods Sold	40-150.595	875354.25	0
2	Cost of Goods Sold	40-150.620	201251.21	39829.86
2	Cost of Goods Sold	40-150.625	133757.27	63900.28
2	Cost of Goods Sold	40-150.630	267930.36	36963.34
2	Cost of Goods Sold	40-150.635	208151.38	1035343.96
2	Cost of Goods Sold	40-150.700	46144.4	803413.85
2	Cost of Goods Sold	40-150.705	408316.4	0
2	Cost of Goods Sold	40-150.710	2404279.28	0
2	Cost of Goods Sold	40-150.740	0	0
2	Cost of Goods Sold	40-150.745	0	94466.08
2	Cost of Goods Sold	40-150.750	447391.1	8064.92
2	Cost of Goods Sold	40-150.755	608989.97	149742.72
2	Cost of Goods Sold	40-150.760	0	0
2	Cost of Goods Sold	40-150.780	823890.1	287858.02
2	Cost of Goods Sold	40-150.820	0	0
2	Cost of Goods Sold	40-150.900	7375759.52	0
2	Cost of Goods Sold	40-160.010	883629.17	348264.87
2	Cost of Goods Sold	40-170.010	38307.57	0
2	Cost of Goods Sold	40-190.010	0	0
3	Gross Profit	null	-218971540	-113759003.3

[RobertOdera · Accepted solution]

Hi, @davidlocke 

Try this.

Please mark as a solution + like, if this works for you.

The output + workflow are attached.

Cheers!

[davidlocke]

What does the [_Current Field_] do?

[RobertOdera]

Hi, @davidlocke 

In your use case, I'm using it to cast targeted fields as a Fixed Decimal type (number with two decimal places) from text, so that I can aggregate them in the Summarize Tool (if they streamed into the Summarize Tool as Text, the SUM option would not be available to me)

🙂Fixed Decimal is what I chose as the best match to the optics of your desired output.

The [_Current Field_]  argument allows me to apply the treatment to both the fields concurrently and with economy.

Otherwise I could also have used a Formula Tool by creating two new fields:

new_field1 = ToNumber([1000]) and make type Fixed Decimal

new_field2 = ToNumber([1100]) and make type Fixed Decimal

I hope this is helpful.

Cheers!