# How do I use regexmatch inside sumproduct?

This works:

``````=sumproduct(\$J\$6:\$J,(\$A\$6:\$A="Order")*(\$G\$6:\$G="T8"))
``````

This does not.

``````=sumproduct(\$J\$6:\$J,(\$A\$6:\$A="Order")*(Regexmatch(\$G\$6:\$G,"T8"))
``````

produces formula parse error.

This

``````=regexmatch(G22:G30,"P")
``````

Produces a single value. -- it's searching the entire range for a match, and outputs TRUE

This

``````=arrayFormula(regexmatch(G22:G30,"P"))
``````

produces a column of 9 True false values.

``````=sumproduct(\$J\$6:\$J,(\$A\$6:\$A="Order")*(ArrayFormula(Regexmatch(\$G\$6:\$G,"T8")))
``````

Also produces a Formula Parse Error.

I'm missing a bit of information. Maybe a byte.

Eventually I want to use more complicated expressions than "P" or "T8"

• I'm not able to reproduce the error. Please provide a sample data input and the expected result. – Rubén Apr 24 '17 at 23:09
• Agree with Rubén, all examples given work for me. Need sample data to reproduce the problem. – ttarchala Apr 25 '17 at 8:50

``````=sumproduct((\$J\$6:\$J)*(\$A\$6:\$A="Order")*(regexmatch(\$G\$6:\$G,"T8")))
``````

My first error was due to unmatched ()'s

pnuts answer works — but is dependent on only having numbers in all fields. Since there are periodic column labels this breaks.

Converting pnut's answer to fields by changing stars to commas works:

``````=sumproduct((\$J\$6:\$J),(\$A\$6:\$A="Order"),(regexmatch(\$G\$6:\$G,"T8")))
``````

Sumproduct appears to take any number of parameters, which are first checked for being numbers, and if any are not numbers that row is skipped.

The internal excess ()'s can be removed.

``````=sumproduct(\$J\$6:\$J,\$A\$6:\$A="Order",Regexmatch(\$G\$6:\$G,"T8")
``````

giving it the same functionality, with less cruft.