Conditional Lookup Question

[godeacs]

Hi All,

I have a spreadsheet with 5 Columns:
A: Customer
B: Contract
C: Sales
D: Type
E: Count

In this sheet, Sales (Col C) and Type (Col D) are mapped to Contract (Col B). The issue is that Sales and Type are pulled from different databases and thus do not match up exactly with Contract. For example, there's a case where the same Contract has two Types and thus double counts Sales. For instances where this occurs, I would like to allocate the Sales based on the Count (Col E).

Here's a shot of what the sample sheet looks like. I can send it to someone if you're willing to take a look.

sample.xls
	A	B	C	D	E	F
1	Customer	Contract	Sales	Type	Count	Comment
2	ABC	11654	$100,000.00	A	60,000
3	CDG	13130	$60,000.00	A	1,000
4	ADFA	11670	$22,222.00	A	70,000,000
5	BFI	11808	$333,333.00	A	123,423
6	BFI	12871	$10,000.00	B	23,122	Inthiscase,the$10,000.00isdoublecountedforBFIContract12871.Tocorrectthis,Iwouldliketoallocatethe$10,000.00basedonthe%ofCountforeachType(23,122/(23,122+7,777,777))
7	BFI	12871	$10,000.00	A	7,777,777	Isthereaneasywaytodothiswithaformula,soIdon'thavetodoitmanually.Theactualspreadsheethas2600rows.
8	MBS	10661	$8,000,000.00	A	100
9	MBS	11627	$3,838.00	A	20,000
10	CSSS	12667	$9,992.00	B	300
11	CSSS	12696	$2,000.00	A	444
12	CSSS	12696	$2,000.00	B	44,444
13	CSSS	12703	$3,333.00	B	1,111
14	CSSS	12718	$11.00	B	222,222
15	CSSS	12756	$48,394,873.00	A	9,999,999
16	CSSS	12796	$34,938.00	B	33,333
17	CCC	11757	$924,785.00	A	2,349,767
18	CCC	11824	$2,945.00	A	454,389
19	CCC	11889	$5,000.00	B	1,111
20	CCC	11889	$5,000.00	C	99,999
21	CCC	11889	$5,000.00	C	100,000
22	CCC	12013	$3,433.00	B	3,432
23	CCC	12042	$98,978.00	B	999
24	CCC	12049	$793,874.00	B	100,000
Sample

Thanks in advance for any help![/img]

 

[Domenic]

Try...

F2, copied down:

=C2*E2/SUMIF($B$2:$B$24,B2,$E$2:$E$24)

Hope this helps!

 

[hooknows]

Domenic,

Thanks so much! This does the trick.