Summarize cost data with spilled formula

[tana]

Hi,

Can someone help me with this problem? I am trying to get to a spilled formula which can summarize a table of cost data. The result will look like this.

Cost by PWOs and Categories. I have managed to get all my PWOs and categories spilled using expand function. But I don't know how to get the cost attached with my current spilled of PWOs and categories. Maybe reduce function? I am not that familiar using REDUCE yet if that is possible. Can someone teach me if that is a solution?

A quick sharing, I don't have pivotby and groupby with my current version of excel yet.

21482296	Project Management & Administrative
21482296	Internal Engineering
21482296	External Engineering
21482296	Construction Labour
21482296	Construction Equipment
21482296	Construction Services
21482296	Materials
21566675	Project Management & Administrative
21566675	Internal Engineering
21566675	External Engineering
21566675	Construction Labour
21566675	Construction Equipment
21566675	Construction Services
21566675	Materials

Here are partial of sample data for your reference.

Book1
	A	B	C
1	Amount	PWO	Category
2	896.1	21482296	IDC
3	1329.88	21566675	IDC
4	5802.94	21482296	IDC
5	23982	21566675	Project Management & Administrative
6	82279.26	21566675	Project Management & Administrative
7	-52852.3	21566675	Project Management & Administrative
8	64577.17	21482296	External Engineering
9	1110	21482296	Project Management & Administrative
10	3528	21482296	Internal Engineering
11	3528	21482296	Internal Engineering
12	3528	21482296	Internal Engineering
13	3528	21482296	Internal Engineering
14	3528	21482296	Internal Engineering
15	3528	21482296	Internal Engineering
16	348	21482296	Internal Engineering
17	116	21482296	Internal Engineering
18	58	21482296	Internal Engineering
19	96888.08	21482296	External Engineering
20	1110	21482296	Project Management & Administrative
21	3528	21482296	Internal Engineering
22	3528	21482296	Internal Engineering
23	3528	21482296	Internal Engineering
24	2352	21482296	Internal Engineering
25	1176	21482296	Project Management & Administrative
26	1176	21482296	Project Management & Administrative
27	3528	21482296	Internal Engineering
28	2352	21482296	Internal Engineering
29	111	21482296	Project Management & Administrative
30	1110	21482296	Project Management & Administrative
31	441	21482296	Project Management & Administrative
32	116	21482296	Internal Engineering
33	116	21482296	Internal Engineering
34	1176	21482296	Internal Engineering
35	3528	21482296	Internal Engineering
36	2352	21482296	Internal Engineering
37	1176	21482296	Internal Engineering
38	1176	21482296	Project Management & Administrative
39	1176	21482296	Project Management & Administrative
40	928	21482296	Internal Engineering
41	1176	21482296	Project Management & Administrative
42	1176	21482296	Project Management & Administrative
43	1176	21482296	Project Management & Administrative
44	580	21482296	Project Management & Administrative
45	116	21482296	Internal Engineering
46	53.5	21482296	Internal Engineering
47	160.5	21482296	Internal Engineering
48	1776	21482296	Project Management & Administrative
49	1665	21482296	Project Management & Administrative
50	928	21482296	Project Management & Administrative
51	348	21482296	Project Management & Administrative
52	336	21482296	Project Management & Administrative
53	224	21482296	Project Management & Administrative
54	928	21482296	Internal Engineering
55	928	21482296	Internal Engineering
56	232	21482296	Internal Engineering
57	928	21482296	Internal Engineering
58	928	21482296	Internal Engineering
59	116	21482296	Internal Engineering
60	464	21482296	Internal Engineering
61	464	21482296	Internal Engineering
62	696	21482296	Internal Engineering
63	116	21482296	Internal Engineering
64	928	21482296	Internal Engineering
65	490.38	21482296	Project Management & Administrative
66	1442.28	21482296	Project Management & Administrative
67	2352	21482296	Internal Engineering
68	3528	21482296	Internal Engineering
69	3528	21482296	Internal Engineering
70	30313.77	21566675	Project Management & Administrative
71	14889.54	21566675	Project Management & Administrative
72	41189.93	21566675	Project Management & Administrative
73	45429.75	21566675	Project Management & Administrative
Sheet1

 

[DanteAmor]

Try:

=LET(a,UNIQUE(B2:C73),HSTACK(a,SUMIFS(A2:A73,B2:B73,CHOOSECOLS(a,1),C2:C73,CHOOSECOLS(a,2))))

 

[DanteAmor]

If you want the headers:

=LET(a,UNIQUE(B2:C73),VSTACK(TEXTSPLIT(TEXTJOIN(",",,B1:C1,A1),","),HSTACK(a,SUMIFS(A2:A73,B2:B73,CHOOSECOLS(a,1),C2:C73,CHOOSECOLS(a,2)))))

 

[tana]

@DanteAmor:

Thanks so much!