Array Question using Search Function

[Alohadboy]

Hello -

I have a fairly complex formula that I was hoping someone could help me with. I have data with multiple names in it and I am trying to sum that data into different categories. Each category has multiple names. For example:

Charlie Brown $10
Charlie Brown $6
Charlie Green $5
John Doe $2
Mark Dil $6

Now, for my result I want the formula to sum all "Charlie" (no matter the last name) and "John"...resulting in $23. This is a very simplistic example as I have multiple names for each category and a data set of about 6,000 rows.

The formula I have so far is:

{=SUMPRODUCT(ISNUMBER(SEARCH({R5,S5,T5,U5,V5,W5,X5,Y5}, $B$3:$B$48))*($E$3:$E$48))}

I want to capture all of my category names (Charlie, John) in the "Search" function, and if they match the data in B3:B48, sum the data in E3:E48. The formula worked until I attempted to add the different names in the "Search" function (R5, S5,...) and this is what I need help with.

Even pressing cntrl/shift/enter the above formula doesn't work. Any help is appreciated. Thanks.

 

[barry houdini]

Try this formula - doesn't need array entry

=SUMPRODUCT(SUMIF($B$3:$B$48,"*"&R5:Y5&"*",$E$3:$E$48)*(R5:Y5<>""))

 

[Alohadboy]

Hi Barry, that didn't work. The reason it didn't work was because I do not want an exact match. For example:

I want all "Charlie", but in my lookup I won't know every last name so I want to run a search on just "Charlie".

I tested it to be an exact match and the formula works but I need this function in it. The formula needs some sort of a wildcard.

Any ideas?

Thanks.

 

[barry houdini]

There are wildcards within the SUMIF function, that means it should sum every cell in E3:E48 where the corresponding value in B3:B48 contains "Charlie" or whatever other names you have in R5:Y5.

Did you test it with the full names? ......it works for me

 

[Alohadboy]

You are correct....I forgot to broaden my range to include the other names.

Thanks a ton.