Sum vlookup formulas

[yappsta]

HI,

I have two tables, one showing types of apartments with the number of appliances in each. At the end of the table the total discharge units associated with each apartment is added together.

Room Types			SHWR	Basin	WC	Sink	DW	WM		Total D/U
DU(l/s)	0.4	0.3	1.7	1.3	0.2	1.2
Cluster			1	1	1					2.4
Studio			1	1	1	1				3.7
Duplex			1	1	1	1				3.7
Penthouse			1	1	1	1				3.7
*******						2	2			3.0
Laundry								4		4.8

<colgroup><col span="18"><col><col></colgroup><tbody>
</tbody>

The second table shows the drainage stacks with each type of apartment that is connected on each floor.

Stack	UG	First	Second	Third	Fourth	Fifth	Sixth	Total D/U
1AE	Cluster	Cluster	Cluster	Cluster	Cluster	Cluster	Duplex	3.70
1AB	Cluster	Cluster	Cluster	Cluster	Cluster	Cluster	Studio	4.80
1AC	Cluster	Cluster	Cluster	Cluster	Cluster	Cluster	Penthouse	#N/A
1CC	Cluster	Cluster	Cluster	Cluster	Cluster	Penthouse	Penthouse	#N/A
2AE		Cluster	Cluster	Cluster	Cluster	Cluster	Duplex	#N/A
2BB		Cluster	Cluster	Cluster	Cluster	Studio	Studio	#N/A
4BE		Studio	Studio	Studio	Studio	Studio	Duplex	#N/A
4BB		Studio	Studio	Studio	Studio	Studio	Studio	#N/A
4BC	-	Studio	Studio	Studio	Studio	Studio	Penthouse	#N/A
5CC	*******	*******	*******	*******	*******	Penthouse	Penthouse	3.00
5DC	*******	*******	*******	*******	*******	*******	Penthouse	3.00
6DD	-	*******	*******	*******	*******	*******	*******	#N/A
6DB	-	*******	*******	*******	*******	*******	Studio	#N/A
1C	Cluster	Cluster	Cluster	Cluster	Cluster	Penthouse	-	2.40
2C	-	Cluster	Cluster	Cluster	Cluster	Penthouse	-	#N/A
3C	Cluster	*******	Cluster	Cluster	Cluster	Penthouse	-	2.40
6C	-	*******	*******	*******	*******	Penthouse	-	#N/A

<colgroup><col span="18"><col><col span="5"><col></colgroup><tbody>
</tbody>

I have tried various vlookup commands to sum the DU in the total column, however as soon as I make the reference cell associated with another floor I get #N/A. If I keep the reference cell the same, so for the same floor, the formula sums correctly.

Any help would be very welcome.

Many thanks

 

[jorismoerings]

Hi and welcome to MrExcel,

See if this helps

Excel 2016 (Windows) 64 bit
	A	B	C	D	E	F	G	H	I
1	Room Types	SHWR	Basin	WC	Sink	DW	WM		Total D/U
2	DU(l/s)	0,4	0,3	1,7	1,3	0,2	1,2		5,1
3	Cluster	0,4	0,3	1,7					2,4
4	Studio	0,4	0,3	1,7	1,3				3,7
5	Duplex	0,4	0,3	1,7	1,3				3,7
6	Penthouse	0,4	0,3	1,7	1,3				3,7
7	*******				2	2			4
8	Laundry						4		4
9
10
11
12
13
14	Stack	UG	First	Second	Third	Fourth	Fifth	Sixth	Total D/U
15	1AE	Cluster	Cluster	Cluster	Cluster	Cluster	Cluster	Duplex	18,1
16	1AB	Cluster	Cluster	Cluster	Cluster	Cluster	Cluster	Studio	18,1
17	1AC	Cluster	Cluster	Cluster	Cluster	Cluster	Cluster	Penthouse	18,1
18	1CC	Cluster	Cluster	Cluster	Cluster	Cluster	Penthouse	Penthouse	19,4
19	2AE		Cluster	Cluster	Cluster	Cluster	Cluster	Duplex	12
20	2BB		Cluster	Cluster	Cluster	Cluster	Studio	Studio	17
21	4BE		Studio	Studio	Studio	Studio	Studio	Duplex	18,5
22	4BB		Studio	Studio	Studio	Studio	Studio	Studio	22,2
23	4BC	-	Studio	Studio	Studio	Studio	Studio	Penthouse	22,2
24	5CC	*******	*******	*******	*******	*******	Penthouse	Penthouse	7,4
25	5DC	*******	*******	*******	*******	*******	*******	Penthouse	3,7
26	6DD	-	*******	*******	*******	*******	*******	*******	0
27	6DB	-	*******	*******	*******	*******	*******	Studio	3,7
28	1C	Cluster	Cluster	Cluster	Cluster	Cluster	Penthouse	-	15,7
29	2C	-	Cluster	Cluster	Cluster	Cluster	Penthouse	-	13,3
30	3C	Cluster	*******	Cluster	Cluster	Cluster	Penthouse	-	13,3
31	6C	-	*******	*******	*******	*******	Penthouse	-	3,7
Sheet1

Cell Formulas
Range	Formula
I15	=SUMPRODUCT((B15:H15=$A$3:$A$6)*$I$3:$I$6)

 

[yappsta]

Thank you Joris, that's taken care of it, much obliged.