SUMIF formula based on multiple contact columns
Hello,
I am working on trying to calucate KPI metrics based on if an employee's name appears in any of 4 contact columns, not just one contact column. I have tried a multitude of SUMIF and SUMIFS formulas and have yet to find anything that works.
So I have a metric sheet listing all of the employee names and let's say row 1 is "John". I need a formula to calcate the sum of a column based on if John's name appears in any of 4 specific columns in the sheet.
I have a helper column combining all the names from those 4 columns into 1 column but still can't find a formula that can do a SUMIF based on if "John" appears anywhere in that column.
Anyone found a solution to this? Thanks!
Best Answer

Hi Kate!
I have an idea on this, hopefully it helps!
In this "ColumnE" is referring to your helper column with the combined names & "ColumnA" is the one you want the sum of.
=SUMIF([ColumnE]:[ColumnE], FIND("John",@cell)>0, [ColumnA]:[ColumnA])
That should get the total of all values in ColumnA that have John in one of columns next to it.
Answers

You'd use an IF statement here in combination with one or more COUNTIFs  the one thing I'm not sure of is if you're trying to sum up a column or a row based on "John" (in your example) appearing anywhere in any of the contact columns or just on a particular row (i.e. the sum portion is adding several columns in that row).

Thanks Nick! Would be curious to hear more about your solution. I’m looking to sum only one column, but search multiple columns for “John”. So if the contact “John” appears in any of columns B, C or D, then give me the sum of those rows from column A. Does that help?

Hi Kate!
I have an idea on this, hopefully it helps!
In this "ColumnE" is referring to your helper column with the combined names & "ColumnA" is the one you want the sum of.
=SUMIF([ColumnE]:[ColumnE], FIND("John",@cell)>0, [ColumnA]:[ColumnA])
That should get the total of all values in ColumnA that have John in one of columns next to it.

That did the trick! Thank you Nick!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 454 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!