SUMIF formula based on multiple contact columns

Kate C. Hasse

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!

Nick055

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.

Nick Korna

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).

Kate C. Hasse

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?

Nick055

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.

Kate C. Hasse

That did the trick! Thank you Nick!