Unable to use pivot table on merged header. Any other ideas?

[Goddess]

Hi,

I have a range of cells, where I'm trying to use pivot to summarize the data. However, pivot table can't be used on merged cells (header). Any idea how to achieve the result I want (under Pivot?)? The Group in column A may change, depending on the entities of the company that I'm analysing. As such, I can't use the standard sum formula, as I may have 5 entities in a group today, tomorrow it may change to 4. Thus, I need a way to sum by the group, regardless of how many entities are there.

Thanks!

sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2	Data
3	Group	Company	Apple				Orange				Total
4			Quantity				Quantity
5			Jan	Feb	Mar	Apr	Jan	Feb	Mar	Apr
6	1	ABC	1	0	5	5	5	4	8	4	32
7	1	DEF	5	0	0	2	0	1	4	2	14
8	2	GHI	6	5	1	1	0	2	2	1	18
9	2	JKL	4	0	5	8	6	0	2	5	30
10	3	MNO	1	5	8	2	5	0	0	8	29
11		Total	17	10	19	18	16	7	16	20	123
12
13
14	Pivot?
15		Apple				Sum for Apple	Orange				Sum for Orange
16		Jan	Feb	Mar	Apr		Jan	Feb	Mar	Apr
17	1	6	0	5	7	18	5	5	12	6	28
18	ABC	1	0	5	5	11	5	4	8	4	21
19	DEF	5	0	0	2	7	0	1	4	2	7
20	2	10	5	6	9	30	6	2	4	6	18
21	GHI	6	5	1	1	13	0	2	2	1	5
22	JKL	4	0	5	8	17	6	0	2	5	13
23	3	1	5	8	2	16	5	0	0	8	13
24	MNO	1	5	8	2	16	5	0	0	8	13
25	Total	17	10	19	18	64	16	7	16	20	59
Sheet1

Cell Formulas
Range	Formula
K6:K11	K6	=SUM(C6:J6)
C11:J11	C11	=SUM(C6:C10)
G20:J20,B20:E20,G17:J17,B17:E17	B17	=SUM(B18:B19)
G23:J23,B23:E23	B23	=SUM(B24)
K17:K24,F17:F25	F17	=SUM(B17:E17)
G25:K25,B25:E25	B25	=B17+B20+B23

 

[SpillerBD]

A Pivot Table Pivots a Table.
Your Data is not in a Table format as Excel defines one.
You will need to Unpivot the Data or have your data sourced into a proper Table.

Watch Mike Girvin's Explanation on Proper Data Sets and other related subjects.

PowerQuery can do the Unpivot and Mike has a video on that too.

 

[Goddess]

A Pivot Table Pivots a Table.
Your Data is not in a Table format as Excel defines one.
You will need to Unpivot the Data or have your data sourced into a proper Table.

Watch Mike Girvin's Explanation on Proper Data Sets and other related subjects.

PowerQuery can do the Unpivot and Mike has a video on that too.

Great! Thanks!