Summarize array data

[tana]

Hi,

Can I have help on a situation which involves with array data?

I have the following categories to summarize (some may have cost data, some may not):

C20288 High Voltage Switch Replacements 202411.xlsx
	X
3	Project Management & Administrative
4	Internal Engineering
5	External Engineering
6	Construction Labour
7	Construction Equipment
8	Construction Services
9	Materials
10	Contingency
11	Allowance
Input

Then I have these cost data in an array:

C20288 High Voltage Switch Replacements 202411.xlsx
	X	Y
16	Project Management & Administrative	500
17	Internal Engineering	500
18	External Engineering	33000
19	Project Management & Administrative	1750
20	Internal Engineering	1750
21	External Engineering	58000
22	Construction Labour	1100
23	Construction Equipment	200
24	Construction Services	0
25	Materials	142000
26	Project Management & Administrative	600
27	Internal Engineering	500
28	External Engineering	28000
29	Project Management & Administrative	500
30	Internal Engineering	500
31	External Engineering	0
32	Project Management & Administrative	520
33	Internal Engineering	500
34	External Engineering	19731.51
35	Project Management & Administrative	700
36	Internal Engineering	500
37	External Engineering	19326.79
38	Project Management & Administrative	680
39	Internal Engineering	500
40	External Engineering	22285.26
41	Project Management & Administrative	630
42	Internal Engineering	500
Input

Cell Formulas
Range	Formula
X16:Y42	X16	=CHOOSECOLS(R3#,2,3)
Dynamic array formulas.

I would like to have spilled formula which can summarize by category. Thanks!

 

[Fluff]

How about

Fluff.xlsm
	X	Y
1
2
3	Project Management & Administrative	5880
4	Internal Engineering	5250
5	External Engineering	180343.6
6	Construction Labour	1100
7	Construction Equipment	200
8	Construction Services	0
9	Materials	142000
10	Contingency	0
11	Allowance	0
12
13
14
15
16	Project Management & Administrative	500
17	Internal Engineering	500
18	External Engineering	33000
19	Project Management & Administrative	1750
20	Internal Engineering	1750
21	External Engineering	58000
22	Construction Labour	1100
23	Construction Equipment	200
24	Construction Services	0
25	Materials	142000
26	Project Management & Administrative	600
27	Internal Engineering	500
28	External Engineering	28000
29	Project Management & Administrative	500
30	Internal Engineering	500
31	External Engineering	0
32	Project Management & Administrative	520
33	Internal Engineering	500
34	External Engineering	19731.51
35	Project Management & Administrative	700
36	Internal Engineering	500
37	External Engineering	19326.79
38	Project Management & Administrative	680
39	Internal Engineering	500
40	External Engineering	22285.26
41	Project Management & Administrative	630
42	Internal Engineering	500
43
Sheet6

Cell Formulas
Range	Formula
Y3:Y11	Y3	=SUMIFS(Y16:Y10000,X16:X10000,X3:X11)
Dynamic array formulas.

 

[tana]

How about

Fluff.xlsm
	X	Y
1
2
3	Project Management & Administrative	5880
4	Internal Engineering	5250
5	External Engineering	180343.6
6	Construction Labour	1100
7	Construction Equipment	200
8	Construction Services	0
9	Materials	142000
10	Contingency	0
11	Allowance	0
12
13
14
15
16	Project Management & Administrative	500
17	Internal Engineering	500
18	External Engineering	33000
19	Project Management & Administrative	1750
20	Internal Engineering	1750
21	External Engineering	58000
22	Construction Labour	1100
23	Construction Equipment	200
24	Construction Services	0
25	Materials	142000
26	Project Management & Administrative	600
27	Internal Engineering	500
28	External Engineering	28000
29	Project Management & Administrative	500
30	Internal Engineering	500
31	External Engineering	0
32	Project Management & Administrative	520
33	Internal Engineering	500
34	External Engineering	19731.51
35	Project Management & Administrative	700
36	Internal Engineering	500
37	External Engineering	19326.79
38	Project Management & Administrative	680
39	Internal Engineering	500
40	External Engineering	22285.26
41	Project Management & Administrative	630
42	Internal Engineering	500
43
Sheet6

Cell Formulas
Range	Formula
Y3:Y11	Y3	=SUMIFS(Y16:Y10000,X16:X10000,X3:X11)
Dynamic array formulas.

I tried and keep coming up with an error message.

The formula I inserted was =SUMIFS(CHOOSECOLS(X16#,2),CHOOSECOLS(X16#,1),X3)

The error message is
There's a problem with this formula.
Not trying to type a formula?
When the first character is an equal("=") or minus ("-") sign, Excel think it's a formula...........

By the way, let me provide the full data which is an array, this is what I have

C20288 High Voltage Switch Replacements 202411.xlsx
	R	S	T	U
3	21656352	Project Management & Administrative	500	500
4	21656352	Internal Engineering	500	500
5	21656352	External Engineering	33000	0
6	21656348	Project Management & Administrative	1750	1000
7	21656348	Internal Engineering	1750	1000
8	21656348	External Engineering	58000	0
9	21656348	Construction Labour	1100	157171
10	21656348	Construction Equipment	200	1000
11	21656348	Construction Services	0	1000
12	21656348	Materials	142000	0
13	21656358	Project Management & Administrative	600	0
14	21656358	Internal Engineering	500	0
15	21656358	External Engineering	28000	0
16	21704689	Project Management & Administrative	500	500
17	21704689	Internal Engineering	500	500
18	21704689	External Engineering	0	14136.24
19	21704691	Project Management & Administrative	520	500
20	21704691	Internal Engineering	500	500
21	21704691	External Engineering	19731.51	19731.51
22	21704693	Project Management & Administrative	700	500
23	21704693	Internal Engineering	500	500
24	21704693	External Engineering	19326.79	19326.79
25	21704695	Project Management & Administrative	680	500
26	21704695	Internal Engineering	500	500
27	21704695	External Engineering	22285.26	22285.26
28	21704697	Project Management & Administrative	630	500
29	21704697	Internal Engineering	500	500
Input

Cell Formulas
Range	Formula
R3:U29	R3	=LET( all,FILTER('Bottom-up Financial AUC'!A2:S202,TAKE('Bottom-up Financial AUC'!A2:S202,,1)<>0), x,COUNTIF(L40:L47,DROP(TAKE(all,,2),,1)), a,FILTER(HSTACK(x,all),x<>0), b,FILTER(a,TAKE(DROP(a,,-1),,-1)<>0), f,DROP(DROP(b,,-3),,1), ff,TAKE(f,,2), n,Input!M3, fe,TAKE(f,,-(12-n+1)), HSTACK(ff,fe))
Dynamic array formulas.

There are few more cols which is the monthly cost by different months.
I would like to summarize it by category with spilled formula if possible. Thanks,

 

[Fluff]

Did you try the formula I suggested?

 

[tana]

Did you try the formula I suggested?

Yes, I think, since my data is an array, I have adjusted your formula to fit the situation, but same concept without hard coded cells.

The one I demonstrate earlier is my trial using sumifs.
By the way, I can't use groupby or pivotby yet. Thanks,

 

[Fluff]

What is wrong with the formula I posted?

 

[tana]

What is wrong with the formula I posted?

oh, nothing is wrong. I just adjusted to fit my sitation with array in there. =SUMIFS(CHOOSECOLS(X16#,2),CHOOSECOLS(X16#,1),X3)

 

[Fluff]

You cannot use an array in sumifs like that.
If there is nothing wrong the formula I posted, why don't you use it?

 

[tana]

You cannot use an array in sumifs like that.
If there is nothing wrong the formula I posted, why don't you use it?

I prefer to use array so I don't need to drag the formula every update and it will be automatically.

 

[Fluff]

You don't need to drag the formula, it's a spill range. Just change the value of the last row well below your expected data.