Need help with creating a lookup based on certain criteria

[KaiNIGHT]

Hi, I hope I can explain myself properly to get some assistance here.
The below data is on my first tab in excel, and I am trying to gain a understanding of how much Actual Revenue a specific marketing event brought in.

[TABLE="width: 904"]
<tbody>[TR]
[TD="class: xl63, width: 159"]Marketing Event[/TD]
[TD="class: xl63, width: 159"]# of Contact[/TD]
[TD="class: xl63, width: 159"]Customer Name[/TD]
[TD="class: xl64, width: 159"] Cost[/TD]
[TD="class: xl64, width: 159"] Projected Revenue[/TD]
[TD="class: xl63, width: 159"]Salesperson(s)[/TD]
[TD="class: xl64, width: 167"] $ Sold - Actual Revenue[/TD]
[TD="class: xl67, width: 87"]% ROI[/TD]
[/TR]
[TR]
[TD]Bowling[/TD]
[TD="align: right"]4[/TD]
[TD]ABC[/TD]
[TD="class: xl65"] $ 375.00[/TD]
[TD="class: xl65"] $ 11,250.00[/TD]
[TD][/TD]
[TD="class: xl65"] $ -[/TD]
[TD="class: xl66, align: right"]-100%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Company A[/TD]
[TD="class: xl65"] $ 1,500.00[/TD]
[TD="class: xl65"] $ 11,250.00[/TD]
[TD][/TD]
[TD="class: xl65"] $ 12,320.00[/TD]
[TD="class: xl66, align: right"]721%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Company B[/TD]
[TD="class: xl65"] $ 375.00[/TD]
[TD="class: xl65"] $ 11,250.00[/TD]
[TD][/TD]
[TD="class: xl65"] $ -[/TD]
[TD="class: xl66, align: right"]-100%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Company C[/TD]
[TD="class: xl65"] $ 375.00[/TD]
[TD="class: xl65"] $ 11,250.00[/TD]
[TD][/TD]
[TD="class: xl65"] $ 2,500.00[/TD]
[TD="class: xl66, align: right"]567%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[TD="class: xl65"] $ -[/TD]
[TD="class: xl66, align: center"]#DIV/0![/TD]
[/TR]
</tbody>[/TABLE]
<style>table { }tr { }col { }br { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-weight: 700; text-align: center; vertical-align: middle; }.xl64 { font-weight: 700; text-align: center; vertical-align: middle; }.xl65 { }.xl66 { }.xl67 { font-weight: 700; text-align: center; vertical-align: middle; }</style>The second tab is my input tab where I log the Marketing Event, Company and, $ Sold - Actual Revenue.

[TABLE="width: 375"]
<tbody>[TR]
[TD="class: xl65, width: 167"]Marketing Event[/TD]
[TD="class: xl65, width: 167"]Customer Name[/TD]
[TD="class: xl66, width: 167"] $ Sold - Actual Revenue[/TD]
[/TR]
[TR]
[TD]Bowling[/TD]
[TD]Company A[/TD]
[TD="class: xl67"] $ 5,000.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Company C[/TD]
[TD="class: xl67"] $ 2,500.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Company A[/TD]
[TD="class: xl67"] $ 7,320.00[/TD]
[/TR]
</tbody>[/TABLE]

here is the formula I used to gain an understanding of how much Company A brought in based on my marketing efforts.

=SUMIF(INPUT!C:C,ROI!D5,INPUT!D:D)

However, I want to add the criteria of Bowling into it. So example, maybe I did a bowling event iwth company A that brought in a certain amount of revenue stream, but then it died down. Well, I had a luncheon with them 5 months later and because of that effort I was able to increase or bring in more revenue. How can I break that out to say I made $ due to the bowling event, and then the luncheon?

Thank you! <style>table { }tr { }col { }br { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl65 { font-weight: 700; text-align: center; vertical-align: middle; }.xl66 { font-weight: 700; text-align: center; vertical-align: middle; }.xl67 { }</style>

 

[KaiNIGHT]

Would SUMIFS work? If so, I am getting a #value error - I obv doing something wrong.

[TABLE="width: 1205"]
<colgroup><col span="3"><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Marketing Effort[/TD]
[TD]# of Contact[/TD]
[TD]Customer Name[/TD]
[TD] Cost [/TD]
[TD] Projected Revenue [/TD]
[TD]Salesperson(s)[/TD]
[TD] $ Sold - Actual Revenue [/TD]
[TD]% ROI[/TD]
[/TR]
[TR]
[TD]Bowling[/TD]
[TD="align: right"]4[/TD]
[TD]ABC[/TD]
[TD] $ 375.00[/TD]
[TD] $ 11,250.00[/TD]
[TD][/TD]
[TD] $ - [/TD]
[TD="align: right"]-100%[/TD]
[/TR]
[TR]
[TD]Bowling[/TD]
[TD][/TD]
[TD]Company A[/TD]
[TD] $ 1,500.00[/TD]
[TD] $ 11,250.00[/TD]
[TD][/TD]
[TD] $ 12,320.00[/TD]
[TD="align: right"]721%[/TD]
[/TR]
[TR]
[TD]Bowling[/TD]
[TD][/TD]
[TD]Company B[/TD]
[TD] $ 375.00[/TD]
[TD] $ 11,250.00[/TD]
[TD][/TD]
[TD="align: center"]#value ![/TD]
[TD="align: center"]#value ![/TD]
[/TR]
[TR]
[TD]Bowling[/TD]
[TD][/TD]
[TD]Company C[/TD]
[TD] $ 375.00[/TD]
[TD] $ 11,250.00[/TD]
[TD][/TD]
[TD] $ 2,500.00[/TD]
[TD="align: right"]567%[/TD]
[/TR]
[TR]
[TD]Luncheon[/TD]
[TD][/TD]
[TD]Company A[/TD]
[TD] $ 500.00[/TD]
[TD] $ 15,000.00[/TD]
[TD][/TD]
[TD] $ 12,320.00[/TD]
[TD="align: right"]2364%[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Marketing Event[/TD]
[TD]Customer Name[/TD]
[TD] $ Sold - Actual Revenue [/TD]
[/TR]
[TR]
[TD]Bowling[/TD]
[TD]Company A[/TD]
[TD] $ 5,000.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Company C[/TD]
[TD] $ 2,500.00[/TD]
[/TR]
[TR]
[TD]Luncheon[/TD]
[TD]Company A[/TD]
[TD] $ 7,320.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[RasGhul]

Hi Kai,

For your sumifs to work correctly there needs to be a matching line on your summary sheet to sum against. Then as you add lines on your revenue log all criteria must be entered per line;

Book1
	A	B	C	D	E	F	G	H
1	Marketing Event	# of Contact	Customer Name	Cost	Projected Revenue	Salesperson(s)	$ Sold - Actual Revenue	% ROI
2	Bowling		Company A	$1,500.00	$11,250.00		$12,320.00
3	Bowling		Company B	$375.00	$11,250.00		$0.00
4	Bowling		Company C	$375.00	$11,250.00		$5,000.00
5	Luncheon		Company A				$12,320.00
6	Luncheon		Company B				$7,320.00
7	Luncheon		Company C				$0.00
Sheet1

Cell Formulas
Range	Formula
G2	=SUMIFS(Sheet2!$C:$C,Sheet2!$A:$A,Sheet1!A2,Sheet2!$B:$B,Sheet1!C2)

Book1
	A	B	C
1	Marketing Event	Customer Name	$ Sold - Actual Revenue
2	Bowling	Company A	$5,000.00
3	Bowling	Company C	$2,500.00
4	Bowling	Company A	$7,320.00
5	Luncheon	Company A	$5,000.00
6	Bowling	Company C	$2,500.00
7	Luncheon	Company A	$7,320.00
8	Luncheon	Company B	$7,320.00
Sheet2