Updated Formula Help - Let(a,CHOOSE

[zone709]

=LET(a,CHOOSE({1,2},H3:H2800,I3:I2800),b,UNIQUE(a),c,INDEX(b,0,1),d,INDEX(b,0,2),CHOOSE({1,2,3},c,d,SUMIFS(J3:J2800,H3:H2800,c,I3:I2800,d)))

Hi need to update formula below. The first sheet is how the formula works now. Returns for me in Column L M & N. As it looks through H i & J then sums total sum.

The second sheet Below is what i need to be updated. I need to also look into Column B and if job is different also with Column i class code. Then return total sum in column O with the adding the job in Column M as you can see Column M as an example.

Sheet 1

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		project_code	contract_id	work_order	week_end_date	check_num	ssn	employee_ID	class_code	gross_employee_pay	all_projects
2	don't put values in this column											Old Formula in here
3		154 2nd						35	Super	5000.00	5000.00	35	Super	$ 5,000.00
4		625 5th						35	Concrete	5000.00	5000.00	35	Concrete	$ 5,000.00
5		625 5th						10	Operator	5562.50	5562.50	10	Operator	$ 5,562.50
6		625 5th						10	Super	6500.00	6500.00	10	Super	$ 6,500.00
7		1253 3rd Street						20	Carpentry	5000.00	5000.00	20	Carpentry	$ 5,000.00
8		4506 75th Street						20	Concrete	1000.00	1000.00	20	Concrete	$ 1,000.00
9		625 5th						15	Super	4750.00	4750.00	15	Super	$ 4,750.00
10		1253 65th Street						15	Carpentry	2437.50	2437.50	15	Carpentry	$ 3,437.50
11		4506 75th Street						15	Carpentry	1000.00	1000.00	0	0.00	$ -
Sheet1

Sheet 2

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	`	project_code	contract_id	work_order	week_end_date	check_num	ssn	employee_ID	class_code	gross_employee_pay	all_projects
2	don't put values in this column											Formula	Need to add this now
3		154 2nd						35	Super	5000.00	5000.00	35	154 2nd	Super	$ 5,000.00
4		625 5th						35	Concrete	5000.00	5000.00	35	625 5th	Concrete	$ 5,000.00
5		625 5th						10	Operator	5562.50	5562.50	10	625 5th	Operator	$12,062.50
6		625 5th						10	Operator	6500.00	6500.00	20	1253 3rd Street	Carpentry	$ 5,000.00
7		1253 3rd Street						20	Carpentry	5000.00	5000.00	20	4506 75th Street	Concrete	$ 1,000.00
8		4506 75th Street						20	Concrete	1000.00	1000.00	15	625 5th	Super	$ 4,750.00
9		625 5th						15	Super	4750.00	4750.00	15	1253 65th Street	Carpentry	$ 2,437.50
10		1253 65th Street						15	Carpentry	2437.50	2437.50	15	4506 75th Street	Carpentry	$ 1,000.00
11		4506 75th Street						15	Carpentry	1000.00	1000.00
Sheet1

 

[SunnyAlv]

try

=LET(data,IFERROR(HSTACK(H3:H100,B3:B100,I3:I100,J3:J1100),0),a,UNIQUE(TAKE(data,,3)),HSTACK(UNIQUE(TAKE(a,,3)),SUMIFS(J3:J100,H3:H100,INDEX(a,,1),B3:B100,INDEX(a,,2),I3:I100,INDEX(a,,3))))

 

[zone709]

try

=LET(data,IFERROR(HSTACK(H3:H100,B3:B100,I3:I100,J3:J1100),0),a,UNIQUE(TAKE(data,,3)),HSTACK(UNIQUE(TAKE(a,,3)),SUMIFS(J3:J100,H3:H100,INDEX(a,,1),B3:B100,INDEX(a,,2),I3:I100,INDEX(a,,3))))

HI thank for the reply. So i entered this formula into Column L but it only gives results in Col L for ID's. I need it to return the results for Column M if different jobs like COlumn N different class code with the SUMIf total in Column O.

The original works like that but was giving me back the ID# and total Sumifs for the ID # with different class codes totals. Now trying to add the Job also in column B as its different alot.

2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	don't put values in this column											New Formula	Example Results	Example Results	Example Results
3		154 2nd						35	Super	5000.00	5000.00	35	154 2nd	Super	$ 5,000.00
4		625 5th						35	Concrete	5000.00	5000.00	35	625 5th	Concrete	$ 5,000.00
5		625 5th						10	Operator	5562.50	5562.50	10	625 5th	Operator	$ 12,062.50
6		1253 3rd Street						10	Carpentry	6500.00	6500.00	10	1253 3rd Street	Carpentry	$ 5,000.00
7		1253 3rd Street						20	Carpentry	5000.00	5000.00	20	1253 3rd Street	Carpentry	$ 1,000.00
8		625 5th						20	Concrete	1000.00	1000.00	20	625 5th	Concrete	$ 4,750.00
9		625 5th						15	Super	4750.00	4750.00	15	625 5th	Carpentry	$ 2,437.50
10		4506 75th Street						15	Carpentry	2437.50	2437.50	15	4506 75th Street	Carpentry	$ 1,000.00
11								15	Carpentry	1000.00	1000.00	15
Formula

 

[SunnyAlv]

The formula I gave is an array, not copied down one by one, see this picture

 

[zone709]

OK i put formula in L3. when i press enter it gives me #SPILL

I alson press ctlr shift and enter but when doing so. It just gives me one ID number in L3

i put formula in L3 and all it returns is 35.

2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	`	project_code	contract_id	work_order	week_end_date	check_num	ssn	employee_ID	class_code	gross_employee_pay	all_projects
2	don't put values in this column											Formula	Need to add this now
3		154 2nd						35	Super	5000.00	5000.00	35
4		625 5th						35	Concrete	5000.00	5000.00
5		625 5th						10	Operator	5562.50	5562.50
6		625 5th						10	Operator	6500.00	6500.00
7		1253 3rd Street						20	Carpentry	5000.00	5000.00
8		4506 75th Street						20	Concrete	1000.00	1000.00
9		625 5th						15	Super	4750.00	4750.00
10		1253 65th Street						15	Carpentry	2437.50	2437.50
11		4506 75th Street						15	Carpentry	1000.00	1000.00
12
Sheet1

 

[SunnyAlv]

OK i put formula in L3. when i press enter it gives me #SPILL

clear cell L4 down and columns M, N, O without any formulas or text,

So that's why the formula produces an Array, the #Spill error means that the Array's range is blocked by several cells that contain values

 

[zone709]

Got it

 

[zone709]

Works thanks so much. This will be so helpful