SUM of VLOOKUP Returning #N/A

[redhots4]

I have a pricing table with three rows at the bottom for optional products. They aren't always included - when they are, the quantity by month is also populated. I'm using a SUM (VLOOKUP) formula to look up the prices for each asset, then multiply by the quantities for a given month to return a monthly subtotal in Row 12. As you can see in my sheet, if cells A9-A11 are blank, the formula returns #N/A.

How do I tell Excel to ignore the blanks in rows 9-11?

SF Practice Operating Model.xlsx
	A	B	C	D
1	Asset	Mon. 1	Mon. 2	Mon. 3
2	Asset #1	3	3	3
3	Asset #2	4	4	4
4	Asset #3	4	4	4
5	Asset #4	7	5	7
6	Asset #5	0	0	0
7	Asset #6	0	5	5
8	Asset #7	4	0	0
9
10
11
12	Total Cost	#N/A	#N/A	#N/A
13
14	Cost Table	Cost
15	Asset #1	10
16	Asset #2	15
17	Asset #3	23
18	Asset #4	14
19	Asset #5	19
20	Asset #6	15
21	Asset #7	4
22	Asset #8	10
23	Asset #9	6
24	Asset #10	13
Sheet1

Cell Formulas
Range	Formula
B12:D12	B12	=SUM(VLOOKUP($A$2:$A$11,$A$15:$B$24,2,FALSE)*$B2:B11)

Cells with Data Validation
Cell	Allow	Criteria
A9:A11	List	=$A$22:$A$24

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[jtakw]

Hi,

Can you show expected results ?

 

[jtakw]

I'm guessing this is what you mean:

Book3.xlsx
	A	B	C	D
1	Asset	Mon. 1	Mon. 2	Mon. 3
2	Asset #1	3	3	3
3	Asset #2	4	4	4
4	Asset #3	4	4	4
5	Asset #4	7	5	7
6	Asset #5	0	0	0
7	Asset #6	0	5	5
8	Asset #7	4	0	0
9
10
11
12	Total Cost	296	327	355
13
14	Cost Table	Cost
15	Asset #1	10
16	Asset #2	15
17	Asset #3	23
18	Asset #4	14
19	Asset #5	19
20	Asset #6	15
21	Asset #7	4
22	Asset #8	10
23	Asset #9	6
24	Asset #10	13
Sheet1046

Cell Formulas
Range	Formula
B12:D12	B12	=SUMPRODUCT(($A2:$A11=$A15:$A24)*($B15:$B24)*B2:B11)

 

[Fluff]

Another option

+Fluff 1.xlsm
	A	B	C	D
1	Asset	Mon. 1	Mon. 2	Mon. 3
2	Asset #1	3	3	3
3	Asset #2	4	4	4
4	Asset #3	4	4	4
5	Asset #4	7	5	7
6	Asset #5	0	0	0
7	Asset #6	0	5	5
8	Asset #7	4	0	0
9
10
11
12	Total Cost	296	623	978
13
14	Cost Table	Cost
15	Asset #1	10
16	Asset #2	15
17	Asset #3	23
18	Asset #4	14
19	Asset #5	19
20	Asset #6	15
21	Asset #7	4
22	Asset #8	10
23	Asset #9	6
24	Asset #10	13
Master

Cell Formulas
Range	Formula
B12:D12	B12	=SUM(XLOOKUP($A$2:$A$11,$A$15:$A$24,$B$15:$B$24,0,0)*$B$2:B11)

 

[redhots4]

Thank you - this works in the demo example that I included. However, when I repurpose this formula on my larger use case, I still get an N/A. I think the reason may be the number of assets I have in the Cost table. Not every asset in the Cost Table will be in the Asset (top) table every time. If the number of rows in the top and bottom tables are mismatched, it returns N/A.

SF Practice Operating Model.xlsx
	A	B	C	D
1	Asset	Mon. 1	Mon. 2	Mon. 3
2	Asset #1	3	3	3
3	Asset #2	4	4	4
4	Asset #3	4	4	4
5	Asset #4	7	5	7
6	Asset #5	0	0	0
7	Asset #6	0	5	5
8	Asset #7	4	0	0
9
10
11
12	Total Cost	#N/A	#N/A	#N/A
13
14	Cost Table	Cost
15	Asset #1	10
16	Asset #2	15
17	Asset #3	23
18	Asset #4	14
19	Asset #5	19
20	Asset #6	15
21	Asset #7	4
22	Asset #8	10
23	Asset #9	6
24	Asset #10	13
25	Asset #11	13
26	Asset #12	12
27	Asset #13	15
28	Asset #14	16
29	Asset #15	11
30	Asset #16	14
31	Asset #17	17
32	Asset #18	15
33	Asset #19	10
34	Asset #20	16
35	Asset #21	17
36	Asset #22	13
Sheet1

Cell Formulas
Range	Formula
B12:D12	B12	=SUMPRODUCT(($A2:$A11=$A15:$A36)*($B15:$B36)*B2:B11)

Cells with Data Validation
Cell	Allow	Criteria
A9:A11	List	=$A$22:$A$24

 

[jtakw]

If @Fluff interpretation is correct, just Anchor the $B2 in my formula:

Book3.xlsx
	A	B	C	D
1	Asset	Mon. 1	Mon. 2	Mon. 3
2	Asset #1	3	3	3
3	Asset #2	4	4	4
4	Asset #3	4	4	4
5	Asset #4	7	5	7
6	Asset #5	0	0	0
7	Asset #6	0	5	5
8	Asset #7	4	0	0
9
10
11
12	Total Cost	296	623	978
13		296	327	355
14	Cost Table	Cost
15	Asset #1	10
16	Asset #2	15
17	Asset #3	23
18	Asset #4	14
19	Asset #5	19
20	Asset #6	15
21	Asset #7	4
22	Asset #8	10
23	Asset #9	6
24	Asset #10	13
Sheet1046

Cell Formulas
Range	Formula
B12:D12	B12	=SUMPRODUCT(($A2:$A11=$A15:$A24)*($B15:$B24)*$B2:B11)
B13:D13	B13	=SUMPRODUCT(($A2:$A11=$A15:$A24)*($B15:$B24)*B2:B11)

 

[Fluff]

Have you tried the formula from post#5, as that will handle different sized data.

 

[redhots4]

Have you tried the formula from post#5, as that will handle different sized data.

Yes - thank you BOTH! The formula you provided, Fluff, works perfectly. Thanks a million! I'll mark yours as the solution, Fluff.

 

[Fluff]

Glad we could help & thanks for the feedback.