Sum of Vlookup where the lookup value is a list which contains blanks

[brillstuff]

Can anyone help me please I have spent so many hours on this. Firstly I am not an excel expert, I can just usually google enough to get by.

I am wanting to be able to input sales data and return a total cost. The sales data will be input as item codes, and each column will be a separate sale. One sheet will have the stock codes and costs, and the other sheet the sales data.

I am trying to use a SUM(VLOOKUP but this only works when the lookup values (sales data) has no blanks. I need the formula to ignore the blanks.

I can only find answers for this when the table array has the blanks, not the lookup values.

I am using the formula =SUM(VLOOKUP(B8:B17,Stock!$A$2:$C$5,3,FALSE))

Thanks in advance

 

[jtakw]

Hi,

What version of Excel are you using, cause I don't see how that formula you posted can work, the syntax does not look correct.
I'm on Excel 2016, whether normally entered or array entered, your formula didn't work for me at all.

Try this, change/adjust cell references/ranges, add sheet name accordingly:

Book3.xlsx
	A	B	C	D	E	F	G	H	I
1	Code		Cost				Sale 1	Sale 2	Sale 3
2	1001		10			Cost	79.5	7	59.5
3	1002		8.5
4	1003		6
5	1004		7
6
7
8							1001	1004	1002
9							1001		1002
10							1002		1002
11							1004		1002
12							1001		1002
13							1001		1002
14							1003		1002
15							1003
16							1003
17							1003
Sheet1035

Cell Formulas
Range	Formula
G2:I2	G2	=SUMPRODUCT((COUNTIF(G$8:G$17,$A2:$A5))*$C2:$C5)

 

[brillstuff]

Hi,

What version of Excel are you using, cause I don't see how that formula you posted can work, the syntax does not look correct.
I'm on Excel 2016, whether normally entered or array entered, your formula didn't work for me at all.

Try this, change/adjust cell references/ranges, add sheet name accordingly:

Book3.xlsx
	A	B	C	D	E	F	G	H	I
1	Code		Cost				Sale 1	Sale 2	Sale 3
2	1001		10			Cost	79.5	7	59.5
3	1002		8.5
4	1003		6
5	1004		7
6
7
8							1001	1004	1002
9							1001		1002
10							1002		1002
11							1004		1002
12							1001		1002
13							1001		1002
14							1003		1002
15							1003
16							1003
17							1003
Sheet1035

Cell Formulas
Range	Formula
G2:I2	G2	=SUMPRODUCT((COUNTIF(G$8:G$17,$A2:$A5))*$C2:$C5)

Thank you so much! This is a much more simple way of doing this.
Thanks again.


Also, I am using excel in outlook.

 

[jtakw]

You're welcome.