SUM Help

[Heirak]

Hi All,

I'm new here but from what I've read, there are quite a few helpful people. I'm confident this isn't a complicated problem but I'm looking for the most efficient way of solving it.

I have an SQL query which returns the sales person's name (Identifier) along with an Amount. Each 'Identifier' can contribute to one sale. So for one sale for example, John and Frank could contribute.

I'm looking for the formula that will be able to look up each 'Identifier' in a single 'Package' and give the resulting 'Sum'

Thanks for taking a look!

Help.xlsx

[Philb1]

If you separated the data in column A into separate columns it would be much easier. You could then use lookups to find the values. You can disguise the columns with formatting if you want the single column look.

[benishiryo]

hi Heirak, welcome to the forum. try this in B2:
=SUMPRODUCT(ISNUMBER(SEARCH($D$2:$D$6,A2))*$E$2:$E$6)

or on the safe side:
=SUMPRODUCT(ISNUMBER(SEARCH(" "&$D$2:$D$6&" "," "&SUBSTITUTE(A2,","," ")&" "))*$E$2:$E$6)
this is needed when you have names like maryann & mary or ann, where another person's name may be within a name

[Heirak]

hi Heirak, welcome to the forum. try this in B2:
=SUMPRODUCT(ISNUMBER(SEARCH($D$2:$D$6,A2))*$E$2:$E$6)

or on the safe side:
=SUMPRODUCT(ISNUMBER(SEARCH(" "&$D$2:$D$6&" "," "&SUBSTITUTE(A2,","," ")&" "))*$E$2:$E$6)
this is needed when you have names like maryann & mary or ann, where another person's name may be within a name

Thank you very much! I'll be able to employ the first suggestion as the name was only an example.

Thank you too Phil for the suggestion. I had thought about doing that but was on my list of not very efficient routes.