Extract and sum a range of cells based on a value from another sheet

[LivingDawn]

Hi all,

I love this site. I have gleened some great solutions from here to the many excel problems I have had over the years. But my current one is doing my head in.

I need to extract and sum a range of cells based on a value from another sheet that matches.

I have a summary worksheet [SALES SUMMARY] with a unique item code, description and the quantity sold.

Excel Workbook
	A	B	C
1	Code	Description	Qty
2	AKTS-880A-01X	Sample 1	3
3	FGML-991B-001	Sample 2	5
4	VBCG-765A-65X	Sample 3	2
SALES SUMMARY

In another worksheet [TRANSACTIONS] I have a list of all of the transactions dumped from another application including unique item code, description, quantity sold per transaction and the total price.

Excel Workbook
	A	B	C	D
1	Code	Description	Qty	Total
2	AKTS-880A-01X	Sample 1	1	$10.12
3	AKTS-880A-01X	Sample 1	1	$10.65
4	AKTS-880A-01X	Sample 1	2	$21.34
5	AKTS-880A-01X	Sample 1	4	$41.36
6	AKTS-880A-01X	Sample 1	1	$10.65
7	AKTS-880A-01X	Sample 1	1	$10.12
8	AKTS-880A-01X	Sample 1	2	$21.30
9	AKTS-880A-01X	Sample 1	1	$10.68
10	FGML-991B-001	Sample 2	1	$ 2.42
11	FGML-991B-001	Sample 2	2	$ 4.96
12	FGML-991B-001	Sample 2	1	$ 2.55
13	FGML-991B-001	Sample 2	1	$ 2.12
14	FGML-991B-001	Sample 2	3	$ 6.69
15	FGML-991B-001	Sample 2	1	$ 2.18
16	FGML-991B-001	Sample 2	1	$ 2.24
17	FGML-991B-001	Sample 2	4	$ 8.96
18	FGML-991B-001	Sample 2	2	$ 4.84
19	VBCG-765A-65X	Sample 3	1	$ 1.12
20	VBCG-765A-65X	Sample 4	3	$ 3.42
21	VBCG-765A-65X	Sample 5	1	$ 1.18
22	VBCG-765A-65X	Sample 6	2	$ 2.38
23	VBCG-765A-65X	Sample 7	1	$ 1.24
24	VBCG-765A-65X	Sample 8	1	$ 1.12
25	VBCG-765A-65X	Sample 9	1	$ 1.16
26	VBCG-765A-65X	Sample 10	2	$ 2.40
27	VBCG-765A-65X	Sample 11	2	$ 2.38
TRANSACTIONS

e.g. So if I want the total value for the first 3 items sold of item code AKTS-880A-01X, I would get a list of:

CODE Description Qty Total
AKTS-880A-01X Sample 1 1 $10.12
AKTS-880A-01X Sample 1 1 $10.65
AKTS-880A-01X Sample 1 1 $10.67 (AKTS-880A-01X Sample 1 2 $21.34)*

Total $31.44

* this one is a quantity of 2, so the total would have to be divided by the quantity

I have tried to do formulas (I am using excel 2003) with the plan of doing a subtotal and extract those that are highlighted or tagged, and then I started to playing with VBA to try and accomplish it.. and failed dismally.

So if anyone can help it would be much appreciated.

Thanks

 

[Special-K99]

Can you base something around

=SUMPRODUCT((Transactions!A2:A27=SalesSummary!$A$2))

That would give you total for each item of code.

Not sure how you would limit the items though but its a start

 

[wildcloud]

I know this approach is ugly, but it works. Hope someone can find an easy and elegant solution.

Excel Workbook
	B	C	D	E	F	G	H
24	AKTS-880A-01X	Sample 1	1	$10.12		Code	AKTS-880A-01X
25	AKTS-880A-01X	Sample 1	1	$10.65		Total Amount	31.44
26	AKTS-880A-01X	Sample 1	2	$21.34
27	AKTS-880A-01X	Sample 1	4	$41.36
28	AKTS-880A-01X	Sample 1	1	$10.65
29	AKTS-880A-01X	Sample 1	1	$10.12
30	AKTS-880A-01X	Sample 1	2	$21.30
31	AKTS-880A-01X	Sample 1	1	$10.68
32	FGML-991B-001	Sample 2	1	$2.42
33	FGML-991B-001	Sample 2	2	$4.96
34	FGML-991B-001	Sample 2	1	$2.55
35	FGML-991B-001	Sample 2	1	$2.12
36	FGML-991B-001	Sample 2	3	$6.69
37	FGML-991B-001	Sample 2	1	$2.18
38	FGML-991B-001	Sample 2	1	$2.24
39	FGML-991B-001	Sample 2	4	$8.96
40	FGML-991B-001	Sample 2	2	$4.84
41	VBCG-765A-65X	Sample 3	1	$1.12
42	VBCG-765A-65X	Sample 4	3	$3.42
43	VBCG-765A-65X	Sample 5	1	$1.18
44	VBCG-765A-65X	Sample 6	2	$2.38
45	VBCG-765A-65X	Sample 7	1	$1.24
46	VBCG-765A-65X	Sample 8	1	$1.12
47	VBCG-765A-65X	Sample 9	1	$1.16
48	VBCG-765A-65X	Sample 10	2	$2.40
49	VBCG-765A-65X	Sample 11	2	$2.38
Sheet1

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[LivingDawn]

Ahh Brilliant... That works perfectly.

A very long formula. It may not be pretty, but it works brilliantly (and is way better than the mess I made)

Thanks wildcloud