SUMIFS formula with multiple contact columns
I know i'm probably missing the simplest solution, but I can't for the life of me figure it out today!
I am trying to create a summary sheet that will show the projected profit in a row where a contact is assigned in either the Account Executive role or Meeting Planner Role. Sometimes, that person may be assigned to both roles. Original sheet pictured.
I want to report the total projected profit if the individual in the Name column on my summary sheet appears in either of the columns on my origination sheet.
The only thing I came up with so far was to do a sumif for each column an then add those together:
=(SUMIFS({EMEA Projected Profit}, {EMEA AE}, FIND(Name@row, @cell) > 0)) + (SUMIFS({EMEA Projected Profit}, {EMEA Meeting Planner}, FIND(Name@row, @cell) > 0))
Summary sheet is setup as follows:
But that is calculating it twice if the individual is assigned to both columns. Any help with an and/or scenario would be greatly appreciated! I know it has to be something simple I'm missing!
Answers
-
One way to solve this is to add a helper column in your source sheet as a Checkbox and mark it checked if the same name is in both columns, then add that condition to your first (or second) formula. Many other ways to do it, but since you have your formula already working, may as well extend it:
=(SUMIFS({EMEA Projected Profit}, {EMEA AE}, FIND(Name@row, @cell) > 0), {Helper Column}, @cell = false) + (SUMIFS({EMEA Projected Profit}, {EMEA Meeting Planner}, FIND(Name@row, @cell) > 0))
I hope that's helpful.
Cheers,
Ramzi
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!