1

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"

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

2 Answers 2

4

Please try:

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.