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.

Marketing Event	# of Contact	Customer Name	Cost	Projected Revenue	Salesperson(s)	$ Sold - Actual Revenue	% ROI
Bowling	4	ABC	$ 375.00	$ 11,250.00		$ -	-100%
		Company A	$ 1,500.00	$ 11,250.00		$ 12,320.00	721%
		Company B	$ 375.00	$ 11,250.00		$ -	-100%
		Company C	$ 375.00	$ 11,250.00		$ 2,500.00	567%
						$ -	#DIV/0!

<tbody>
</tbody>

<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.

Marketing Event	Customer Name	$ Sold - Actual Revenue
Bowling	Company A	$ 5,000.00
	Company C	$ 2,500.00
	Company A	$ 7,320.00

<tbody>
</tbody>

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.

Marketing Effort	# of Contact	Customer Name	Cost	Projected Revenue	Salesperson(s)	$ Sold - Actual Revenue	% ROI
Bowling	4	ABC	$ 375.00	$ 11,250.00		$ -	-100%
Bowling		Company A	$ 1,500.00	$ 11,250.00		$ 12,320.00	721%
Bowling		Company B	$ 375.00	$ 11,250.00		#value !	#value !
Bowling		Company C	$ 375.00	$ 11,250.00		$ 2,500.00	567%
Luncheon		Company A	$ 500.00	$ 15,000.00		$ 12,320.00	2364%

<colgroup><col span="3"><col span="2"><col><col><col></colgroup><tbody>
</tbody>

Marketing Event	Customer Name	$ Sold - Actual Revenue
Bowling	Company A	$ 5,000.00
	Company C	$ 2,500.00
Luncheon	Company A	$ 7,320.00

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

 

[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