Summarize Data from table - deal with dublicates

[nikolacm]

The following table presents room type categories (Local Name) and sub categories (1-bd to 5-bd). In terms of categories several dublicates are situated. How can we deal with them ?
The new table should indicate Local Name (cleared from dublicates) and for each subcategory (1-bd to 5-bd) the total number of units. The latter can be done by using sumif function.

Local Name	Total	Total GFA	1-bd	2-bd	3-bd	4-bd	5-bd
Slot 3 Apartments	71	7,300.00	35	27	9	-	-
Slot 3 Apartments	35	3,970.00	11	17	7	-	-
Slot 3 Villas	5	1,530.00	-	-	2	3	-
Slot 3 Villas	4	1,260.00	-	-	1	3	-
Slot 3 Villas	10	2,940.00	-	-	6	4	-
Slot 3 Villas	1	550.00	-	-	-	-	-
Slot 3 Villas	4	2,300	-	-	-	-	3
Slot 2 Apartments	13	1,360.00	6	5	2	-	-
Slot 2 Apartments	19	2,060.00	8	7	4	-	-
Slot 2 Apartments	44	5,255.00	11	23	10	-	-
Slot 2 Apartments	29	3,395.00	9	13	7	-	-
Slot 2 Villas	18	4,590.00	-	9	9	-	-
Slot 2 Apartments	14	2,052.00	2	6	6	-	-
Slot 2 Villas	4	1,000.00	-	2	2	-	-
Slot 2 Villas	1	650.00	-	-	-	-	-
Slot 2 Apartments	24	3,915.00	-	9	15	-	-
Slot 2 Apartments	9	1,315.00	1	4	4	-	-
Slot 2 Apartments	11	1,900	-	3	8	-	-

 

[Alex Blakenburg]

If you make your data table an actual Excel Table so that your formulas automatically expand you can do something like this:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Local Name	Total	Total GFA	1-bd	2-bd	3-bd	4-bd	5-bd			Local Name	Total	Total GFA	1-bd	2-bd	3-bd	4-bd	5-bd
2	Slot 3 Apartments	71	7,300.00	35	27	9					Slot 3 Apartments	106	11270	46	44	16	0	0
3	Slot 3 Apartments	35	3,970.00	11	17	7					Slot 3 Villas	24	8580	0	0	9	10	3
4	Slot 3 Villas	5	1,530.00			2	3				Slot 2 Apartments	163	21252	37	70	56	0	0
5	Slot 3 Villas	4	1,260.00			1	3				Slot 2 Villas	23	6240	0	11	11	0	0
6	Slot 3 Villas	10	2,940.00			6	4
7	Slot 3 Villas	1	550
8	Slot 3 Villas	4	2,300					3
9	Slot 2 Apartments	13	1,360.00	6	5	2
10	Slot 2 Apartments	19	2,060.00	8	7	4
11	Slot 2 Apartments	44	5,255.00	11	23	10
12	Slot 2 Apartments	29	3,395.00	9	13	7
13	Slot 2 Villas	18	4,590.00		9	9
14	Slot 2 Apartments	14	2,052.00	2	6	6
15	Slot 2 Villas	4	1,000.00		2	2
16	Slot 2 Villas	1	650
17	Slot 2 Apartments	24	3,915.00		9	15
18	Slot 2 Apartments	9	1,315.00	1	4	4
19	Slot 2 Apartments	11	1,900		3	8
Table

Cell Formulas
Range	Formula
K2:K5	K2	=UNIQUE(Table1[Local Name])
L2:R5	L2	=SUMIFS(INDEX(Table1,0,MATCH(L$1,Table1[#Headers],0)),Table1[Local Name],$K2#)
Dynamic array formulas.