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"

  • 1
    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
  • 1
    Agree with Rubén, all examples given work for me. Need sample data to reproduce the problem. – ttarchala Apr 25 '17 at 8:50
3

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, privacy policy and cookie policy

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