SEARCH Function failing due to "-" (dash)

[chris3131]

Hi,
I have an issue with a complex SUMPRODUCT formula relating to the inclusion of a dash within the variable parameter.
I have boiled the problem down to the SEARCH function included within the SUMPRODUCT and provide a simplified example of the issue here:
Given the following data:

	A	B
1	Car	1
2	Bus	2
3	Train	3
4	Bus-2	4

<tbody>
</tbody>

I am using the following formula:
=SUMPRODUCT(--(ISNUMBER(SEARCH($A$1:$A$4,[variable]))),$B$1:$B$4)

When the variable = "Car;Bus" it returns 3...correct
When the variable = "Car;Bus;Train" it returns 6...also correct
However when the variable = "Bus-2" it returns 6. ie. it is summing Bus-2 and Bus when I just want it to return Bus-2.

The variable needs to be dynamic and use single or multiple parameters (separated by a semicolon) as desired by the user.
The dash is coming from the raw data and cannot be changed.

As I mentioned, the formula is more complex than the example above and includes multiple variable parameters within the single SUMPRODUCT. It is also heavily used within my spreadsheet which is already pretty intensive in terms of processing, so any solution needs to be relatively neat and preferably not slow it down any further.

Please let me know if you have any ideas...

Thanks

 

[RoryA]

I think you'd have to use something like this:

=SUMPRODUCT(--(ISNUMBER(SEARCH(";"&$A$1:$A$4&";",";"&[variable]&";"))),$B$1:$B$4)

assuming the delimiter is always a semicolon.

 

[chris3131]

Thanks Rory, that works a treat.

 

[RoryA]

Glad to help.