IF Sum Vlookup Query

[trish123]

I have an excel file with product codes and descriptions of products.
Each month I receive data of all the items that are sold over the course of the month.
What I want to do is create a vlookup based on the product code in my main sheet then look at the product code on the sales data sheet, to then return the value column of each time a purchase was created based on the different products. Also Without returning 'NA'

 

[awoohaw]

The SUMIFS or SUMIF function may work better for you.
It can be dragged down row by row. Or in an aggregate function (committed with the CNTL-SHFT-ENTR keystroke -CSE).

=IFERROR(SUMIFS(Spend!$E$1:$E$37,Spend!$A$1:$A$37,$A$2:$A$8),"")

Book1
	A	B	C	D	E
1	VAE				5
2	SUQ				5
3	KBX				5
4	HRF				7
5	TAR				5
6	YQG				8
7	OXM				5
8	CTG				8
9	XKC				10
10	CYW				9
11	VAE				8
12	SUQ				6
13	KBX				7
14	HRF				9
15	TAR				7
16	YQG				9
17	OXM				6
18	CTG				7
19	XKC				8
20	CYW				8
21	DJM				8
22	HIT				10
23	ZFN				5
24	TXP				8
25	KSS				6
26	NJW				7
27	OYB				10
28	VAE				8
29	SUQ				8
30	KBX				5
31	HRF				9
32	TAR				5
33	YQG				9
34	OXM				6
35	CTG				8
36	XKC				7
37	CYW				8
Spend

Book1
	A	B
1
2	CTG	23
3	CYW	25
4	DJM	8
5	HIT	10
6	HRF	25
7	KBX	17
8	KSS	6
Summary

Cell Formulas
Range	Formula
B2:B8	B2	=IFERROR(SUMIFS(Spend!$E$1:$E$37,Spend!$A$1:$A$37,Summary!A2),"")

 

[awoohaw]

Here is a comparison of SUMPRODUCT and the SUMIFS
The SUMPRODUCT Cannot be entered in just one cell, it must be copied down.
And from what i've seen the SUMPRODUCT uses more processing time than SUMIFS. Although I've not seen clear documentation. Regardless, if your worksheet is not huge it should not affect performance.

(Later Edit: You may be able to remove the error trapping in the SUMIFS, and just use:

=SUMIFS(Spend!$E$1:$E$37,Spend!$A$1:$A$37,$A$2:$A$8)

)

Book1
	A	B	C
1		SUMPRODUCT	SUMIFS
2	CTG	23	23
3	CYW	25	25
4	DJM	8	8
5	HIT	10	10
6	HRF	25	25
7	KBX	17	17
8	KSS	6	6
Summary

Cell Formulas
Range	Formula
B2:B8	B2	=SUMPRODUCT((Spend!$E$1:$E$37)*(Spend!$A$1:$A$37=$A2))
C2:C8	C2	=IFERROR(SUMIFS(Spend!$E$1:$E$37,Spend!$A$1:$A$37,$A$2:$A$8),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[trish123]

The SUMIFS or SUMIF function may work better for you.
It can be dragged down row by row. Or in an aggregate function (committed with the CNTL-SHFT-ENTR keystroke -CSE).

=IFERROR(SUMIFS(Spend!$E$1:$E$37,Spend!$A$1:$A$37,$A$2:$A$8),"")

Book1
	A	B	C	D	E
1	VAE				5
2	SUQ				5
3	KBX				5
4	HRF				7
5	TAR				5
6	YQG				8
7	OXM				5
8	CTG				8
9	XKC				10
10	CYW				9
11	VAE				8
12	SUQ				6
13	KBX				7
14	HRF				9
15	TAR				7
16	YQG				9
17	OXM				6
18	CTG				7
19	XKC				8
20	CYW				8
21	DJM				8
22	HIT				10
23	ZFN				5
24	TXP				8
25	KSS				6
26	NJW				7
27	OYB				10
28	VAE				8
29	SUQ				8
30	KBX				5
31	HRF				9
32	TAR				5
33	YQG				9
34	OXM				6
35	CTG				8
36	XKC				7
37	CYW				8
Spend

Book1
	A	B
1
2	CTG	23
3	CYW	25
4	DJM	8
5	HIT	10
6	HRF	25
7	KBX	17
8	KSS	6
Summary

Cell Formulas
Range	Formula
B2:B8	B2	=IFERROR(SUMIFS(Spend!$E$1:$E$37,Spend!$A$1:$A$37,Summary!A2),"")

That works a treat thank you so much for all the help

 

[awoohaw]

That works a treat thank you so much for all the help

My pleasure, happy to help.
(I just made an edit regarding the ERROR trapping, it may not be needed).

Best Wishes!

 

[Peter_SSs]

I can't seem to get that working

First look here. If that does not solve the problem then ..
What are the symptoms and when do they occur?

I am using excel 2016.

Please update your forum profile (click your user name at the top right of the forum, then ‘Account details’) 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’)