Vlookup & sum of multiple sales in various columns

[peterECD]

Hi,
Need a solution for the following please;
1. Search a product from a product list a-z in a product tab
2. Find all sales for this product in the Sales Tab in the same sheet.
3. Sales for products have multiple entries (quantities) in columns E, H, K, N, Q...etc every 3rd column.
4 Add all sales by product and return a total next to the product in the product tab.

Ive tried SUMIF and sum(VLOOKUP) but cant get a solution.

Hoep someone can assist please!
Thanks
Peter

 

[Special-K99]

"Sales for products have multiple entries (quantities) in columns E, H, K, N, Q...etc every 3rd column."
So sales for a product go horizontally.

Can a product code appear more than once in the Sales tab, e.g.

                   Sales
Shirts             3     11     12     9
Trousers           2     14
Shirts             6     5

 

[peterECD]

Hi,
Yes it can. The product appears in multiple columns eg Columns 1(C),4(F),7(I),10(L),13(O) etc and the quantity associated with that product is in columns 3(E), 6(H), 9(K), 12(N), Q...etc every 3rd column.

For example;
Shirts 3 Trousers 12 Shirts 9
Trousers 2 Shirts 14 Belts 8
Shirts 6 Belts 5
Belts 4

Show Total sales on the Product tab;
Shirts 32
Trousers 14
Belts 17
etc
Thanks hope this is clearer.

 

[FlameRetired]

Not sure I have the concept and it doesn't sound like SUMIF or VLOOKUP are the tools for the job.

Try in B2 and filled down

=SUM(INDEX($C$2:$O$11,,MATCH($A2,$C$1:$O$1,0)))

Can you adapt this to your tabs?

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

1​

Shirts​

Trousers​

Belts​

Shoes​

Hats​

2​

Shirts​

77​

6​

6​

7​

10​

6​

3​

Trousers​

61​

11​

13​

10​

11​

6​

4​

Belts​

87​

7​

3​

8​

5​

0​

5​

Shoes​

92​

12​

0​

14​

10​

1​

6​

Hats​

48​

8​

10​

13​

11​

3​

7​

9​

0​

5​

7​

9​

8​

0​

9​

9​

13​

6​

9​

11​

7​

9​

8​

0​

10​

13​

7​

6​

6​

9​

11​

0​

6​

6​

11​

8​

 

[peterECD]

Hi
Ive worked how how to insert my files'
Heres the Product Tab;
Excel 2010

	A	B
1	Items	QTY
2	144XT
3	145SJ
4	145SS
5	14DI
6	14JF
7	14RS
8	14SE
9	14SI2
10	14SW1
11	14SWD
12	14SWE
13	14SWS
14	151PJ
15	151PM
16	151PS
17	151SJR
18	151SS
19	151SS3
20	151SS4
21	151SS5

<tbody>
</tbody>

Items​

Heres is the SAles Tab;
Item1, Item 2 etc are the products, Total per Kit 1 are the sales by item;
Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Qty Invoiced	Kit Reporting Code	Item 1	Qty	Total per Kit 1	Item 2	Qty	Total per Kit 2	Item 3	Qty	Total per Kit 3	Item 4	Qty	Total per Kit 4
2		24VB10k	24VB	10	0			0			0			0
3		24VF10k	24VF	10	0			0			0			0
4		24VG10k	24VG	10	0			0			0			0
5		24VO10k	24VO	10	0			0			0			0
6		24VR10k	24VR	10	0			0			0			0
7		24VRGY10k	24VRGY	10	0			0			0			0
8		24VRGYS10k	24VRGYS	10	0			0			0			0
9		24VRRB10k	24VRRB	10	0			0			0			0
10		24VRRBS10k	24VRRBS	10	0			0			0			0
11		24VY10k	24VY	10	0			0			0			0
12	11	43AHBk	40AR	6	66	40AY	6	66	401A	6	66			0
13	12	43BB3k	181N3	10	120	64MS	1	12			0			0
14	32	43BB5k	181N5	10	320	64MS	1	32			0			0
15	13	43BB5Ck	181N5C	10	130	64MS	1	13			0			0
16	1	43BB6k	181N6	10	10	64MJ	1	1			0			0
17	7	43BB6Ck	181N6C	10	70	64MJ	1	7			0			0
18	4	43BB7k	181N7	10	40	64MJ	1	4			0			0
19	4	43BB7Ck	181N7C	10	40	64MJ	1	4			0			0
20	11	43BBLk	28BBC	1	11	28BBL	1	11	28BBN	1	11	28BBN11	1	11

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Kits Invoiced​

Hope this makes more sense mow.

 

[FlameRetired]

No I am not making sense of this. I am not seeing the connections between the Product tab and sales tab. I see nothing in common between the 2 ... no items match.

 

[peterECD]

Sorry, hope I can define this better;
The product tab is the summary of sales by product in the full spreadsheet on Sales Tab(kits invoiced);
Here is a grab of the product list from the product tab, for example 64MS is shown in the sales spreadsheet in row F, but could also be anywhere in the sheet. So the formula needs to search for every entry which may exist in this sales tab and total the qty sold.
Excel 2010

	A
210	64CBT
211	64CBTL
212	64CBTX
213	64FBC
214	64HC
215	64MJ
216	64MS
217	64MSS
218	64SL
219	64SS
220	65A
221	65AD
222	66KB
223	671B
224	673B
225	673C
226	691YB
227	691YG
228	691YR

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>

Items​

 

[FlameRetired]

Referencing the Sales tab posted in Post #5 and using the most recent Product list Post #7

	Q​	R​	S​
1​	Product​	Sums​
2​	64CBT​	0​	In R2: =SUMIF($C$2:$L$20,Q2,$E$2:$N$20)​
3​	64CBTL​	0​
4​	64CBTX​	0​
5​	64FBC​	0​
6​	64HC​	0​
7​	64MJ​	16​
8​	64MS​	57​
9​	64MSS​	0​
10​	64SL​	0​
11​	64SS​	0​
12​	65A​	0​
13​	65AD​	0​
14​	66KB​	0​
15​	671B​	0​
16​	673B​	0​
17​	673C​	0​
18​	691YB​	0​
19​	691YG​	0​
20​	691YR​	0​