Sumproduct of 2 columns, if one of 7 strings found in corresponding rows

Hi all,

I can't get my head around the correct syntax for this type of formula:

=SUMPRODUCT(SIGN(ISNUMBER(SEARCH({"454";"453";"452";"451";"254";"253";"252"};INDIRECT("'"&A51&"'!B3:B"&D51-1))));INDIRECT("'"&A51&"'!H3:H"&D51-1);INDIRECT("'"&A51&"'!"&C51&3&":"&C51&D51-1))

or to simplify:

=SUMPRODUCT(SIGN(ISNUMBER(SEARCH({"454";"453";"452";"451";"254";"253";"252"};B3:B69)));H3:H69;I3:I69)

In column B there is a text (Type no.) that I want to scan for the occurrence (yes/no) of one of these 7 substrings, and for the rows that are returned, multiply columns H and I (price per item [H] times number of items [I]).
In this way, the formula will return the total summed price for all items that match the search conditions.

If I run this command without the array, so only for "254" for example, all goes well. Why doesn't it in this form?

I think it's the SIGN, it doesn't return as an array. And the ; within the array.

Try ISNUMBER

=SUMPRODUCT((ISNUMBER(SEARCH({"454","453","452","451","254","253","252"};B3:B69)))*H3:H69*I3:I69)

Unfortunately that does not work. Within the Dutch locale I have to use ; instead of , because of regional settings. Within the array I'm using semi-colons as well.

I've tried the same thing with a SUMIF statement, but that does not let me do any multiplication of rows within it's syntax; the sum_range criterion only allows a reference to a cell range, not a newly constructed array.

Is there any other way to do this within one cell?

I have tried it and Excel shows a syntax error; setting your machine to Dutch locale apparently does not allow , as a seperator. Any other tips?

OK, it's more than just list seperator issues then..

This works when I set to Dutch(Belgium)

=SUMPRODUCT((ISNUMBER(SEARCH({"454"\"453"\"452"\"451"\"254"\"253"\"252"};B3:B69)))*H3:H69*I3:I69)

Your comment got me thinking and in my simplified example it works if I use TRANSPOSE() to get the column-wise array you are talking about!

It works in the extended formula as well (with all my INDIRECT references), but only if I use * instead of ; for the sumproduct, and if I use the TRANSPOSE() function! Thanks

Correct syntax therefore is:
=SUMPRODUCT((ISNUMBER(SEARCH(TRANSPOSE({"454";"453";"452";"451";"254";"253";"252"});B3:B69)))*H3:H69*I3:I69)

Can you explain why the * is needed instead of the ; and why this array needs to be transposed?

Thanks for your elaborate explanation, it's clear now!

You're welcome.