SUMIFS formula with multiple contact columns

S_Karkhoff

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!

Ramzi K

@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