Need help with creating a lookup based on certain criteria

K

KaiNIGHT

New Member
Joined
Jan 2, 2016
Messages
6
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>
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
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]
 
Upvote 0
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
ABCDEFGH
1Marketing Event# of ContactCustomer NameCostProjected RevenueSalesperson(s)$ Sold - Actual Revenue% ROI
2BowlingCompany A$1,500.00$11,250.00$12,320.00
3BowlingCompany B$375.00$11,250.00$0.00
4BowlingCompany C$375.00$11,250.00$5,000.00
5LuncheonCompany A$12,320.00
6LuncheonCompany B$7,320.00
7LuncheonCompany C$0.00
Sheet1
Cell Formulas
RangeFormula
G2=SUMIFS(Sheet2!$C:$C,Sheet2!$A:$A,Sheet1!A2,Sheet2!$B:$B,Sheet1!C2)



Book1
ABC
1Marketing EventCustomer Name$ Sold - Actual Revenue
2BowlingCompany A$5,000.00
3BowlingCompany C$2,500.00
4BowlingCompany A$7,320.00
5LuncheonCompany A$5,000.00
6BowlingCompany C$2,500.00
7LuncheonCompany A$7,320.00
8LuncheonCompany B$7,320.00
Sheet2
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
570
smozgur
smozgur
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
T
  • Question Question
Pasting Text Changes sheet font
Replies
1
Views
734
6StringJazzer
6StringJazzer
W
  • Question Question
Formula Required to Group Family Members, where ID matches
2
Replies
13
Views
2K
jtakw
jtakw
E
  • Question Question
How to copy values from one workbook sheet to another, based on criterias ?
Replies
7
Views
2K
DanteAmor
DanteAmor

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top