SUMIF based on substring-match criteria (CSE solution)

[mill]

Hi -

I'm trying to use a CSE function to sum a given range based on criteria applied to a separate range of the same dimensions (i.e. the {=SUM(IF(...))} convention). The challenge is that the criteria to apply is a list of user-defined substrings present in the range to which the criteria match applies (something akin to the most recent monthly challenge posted here -> http://www.mrexcel.com/forum/showthread.php?p=1597483#post1597483, but see below for variant illustration). At present, I have only arrived at the following formula:

{=SUM(IF(NOT(ISERROR(SEARCH(arrayA,arrayB))),arrayC))}

For ease of reference to the illustration,
arrayA = $A$1:$A$5 - array containing substring criteria
arrayB = $B$1:$B$10 - array containing string values
arrayC = $C$1:$C$10 - array containing scalar values paired with arrayB strings

However, the SEARCH (and FIND) functions do not apply arrayA to arrayB in a desired manner (though Excel's implementation seems reasonable and appropriate behavior). For my purposes, I am looking for an implementation that performs an "OR-like" analysis (i.e. if any given substring from arrayA is present in any of the strings in arrayB, sum the corresponding values in arrayC). It would be interesting to see an "AND-like" solution that sums arrayC values if, and only if, all substrings in arrayA are present in arrayB as well.

Also, I avoid user-defined functions at all costs due to performance and security reasons.


Illustration (note that cell $B$1 in this example is blank):

tmpwkbk.xls
	A	B	C	D
1	sky		1
2	red	Thereisnokeyword.	2
3	balloon	Theskyisorange.	4
4	high	Theseaisred.	8
5	fly	Theballoonissmall.	16
6		Therabbithopshigh.	32
7		Abirdwillfly.	64
8		Theballoonwillflyhighintheredsky.	128
9		Hernicknameis"Redsky"	256
10		fly	512
Sheet1

So, using a CSE formula that uses all the elements of arrayA will yield a result of 1020. The following is sample output for different variants of arrayA (arrays arrayB and arrayC remain static):
$A$1:$A$1 -> 388
$A$1:$A$2 -> 396
$A$1:$A$3 -> 412
$A$1:$A$4 -> 444
$A$1:$A$5 -> 1020

The "AND-like" analysis would yield the following for different variants of arrayA:
$A$1:$A$1 -> 388
$A$1:$A$2 -> 384
$A$1:$A$3 -> 128
$A$1:$A$4 -> 128
$A$1:$A$5 -> 128

Please let me know if I may clarify any of the above, and thanks for the help.

 

[Domenic]

Try....

E2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(IF(MMULT(ISNUMBER(SEARCH(TRANSPOSE($A$1:A1),$B$2:$B$10))+0,ROW($A$1:A1)^0),$C$2:$C$10))

and

F2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(IF(MMULT(ISNUMBER(SEARCH(TRANSPOSE($A$1:A1),$B$2:$B$10))+0,ROW($A$1:A1)^0)=ROWS($A$1:A1),$C$2:$C$10))

Hope this helps!

 

[mill]

Good man! Thanks for the help and ultra-quick response.