SUMIFS formula with multiple contact columns

Options

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

  • Ramzi K
    Ramzi K ✭✭✭✭✭
    edited 10/14/20
    Options

    @S_Karkhoff

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!