SumIF with multiple columns with non-specific text criteria

Hi all,

Long time lurker but first time poster after finally registering. I am having an issue using SUMIF...

Column D has people's full names (ex. "KAPLAN BARRY M"). Columns G:R contain numbers. For every instance that "KAPLAN BARRY M" exists in D, I want to sum the values in G:R.

Here is the kicker...the data in excel is aggregated from multiple sources, so "KAPLAN BARRY M" could also be "BARRY M KAPLAN" or "KAPLAN, BARRY M" for instance. Therefore, I tried using the formula below, to "search" by last name. I know the issue lies within the astericks on either side of the name. Any suggestions?


=SUMPRODUCT(($D$4:$D$4388="*KAPLAN*")*$G$4:$R$4388)

TIA,
Lou

Sumproduct doesn't work with wildcards like that.

Try
=SUMPRODUCT((ISNUMBER(SEARCH("Kaplan",$D$4:$D$4388)))*$G$4:$R$4388)

Change Search to Find if it needs to be case sensitive.

That worked, thanks! Now to the more important part...actually understanding what the formula provided is doing.

Within column D, the formula searches for "Kaplan". The search function provides a number for where the text "Kaplan" begins if it exists in a cell, resulting in a TRUE statement from ISNUMBER. Anywhere we get a TRUE, the SUMPRODUCT sums the corresponding columns (G:R). Am I on the right track?

Originally Posted by Jonmo1

Sumproduct doesn't work with wildcards like that.

Try
=SUMPRODUCT((ISNUMBER(SEARCH("Kaplan",$D$4:$D$4388)))*$G$4:$R$4388)

Change Search to Find if it needs to be case sensitive.

You're welcome.

And that is pretty much exactly what it does.

Ok, great. I will have a second part to this question once I get through this workbook. I hope I can pick your brain a bit more!

Ok, part dos.


Let's say Barry's name is displayed in the following 3 ways:
KAPLAN, BARRY M
KAPLAN BARRY M
KAPLAN BARRY

Let's also say there are two "false positives" that get picked up in the formula above due to someone having the same last name:
KAPLAN ANDREA
KAPLAN FRED

How could I go about adjusting that formula so only Barry's data is summed in columns G:R and not Andrea or Fred's?

See attachment:
Book4.xlsx

We can just add another criteria for the first name, just like for the last name

=SUMPRODUCT((ISNUMBER(SEARCH("Kaplan",$D$4:$D$4388)))*(ISNUMBER(SEARCH("Barry",$D$4:$D$4388)))*$G$4:$R$4388)

I never realized it was that easy. Thanks again, rep added.

Originally Posted by Jonmo1

We can just add another criteria for the first name, just like for the last name

=SUMPRODUCT((ISNUMBER(SEARCH("Kaplan",$D$4:$D$4388)))*(ISNUMBER(SEARCH("Barry",$D$4:$D$4388)))*$G$4:$R$4388)

Your welcome.