# SUMIF based on substring-match criteria (CSE solution)

#### mill

##### New Member
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
ABCD
1sky1
2redThereisnokeyword.2
3balloonTheskyisorange.4
4highTheseaisred.8
5flyTheballoonissmall.16
6Therabbithopshigh.32
7Abirdwillfly.64
8Theballoonwillflyhighintheredsky.128
9Hernicknameis"Redsky"256
10fly512
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.

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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!

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

Replies
8
Views
2K
Replies
15
Views
1K
Replies
11
Views
386
Replies
6
Views
7K
Replies
6
Views
1K

1,207,255
Messages
6,077,311
Members
446,278
Latest member
hoangquan2310

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back