I need to use FILTER() to sum some cells only if two conditions are TRUE. This code works for one condition =sum(filter(H4:H, regexmatch(G4:G, "TextA")) but I can't get it to work on two condition:=sum(filter(H4:H, regexmatch(I4:I, "TextA") + regexmathch(J4:J, "TextB")))

My code should sum all the cells from H(starting in H4) only if in the row, this cell, the column I and J contain TextA and TextB respectively.


You don't need to use FILTER(), this looks like the perfect case for SUMIFS().

=sumifs(H1:H6, I1:I6, "TextA", J1:J6, "TextB")

enter image description here

  • Any idea what are the performance differences between sumifs() and sum(filter())? – Yaron Apr 20 '19 at 9:35
  • I haven't tested, but an educated guess would be that sumifs() will be quicker, especially with regexp functions inside of filter() – ttarchala Apr 21 '19 at 11:01

How about using AND?

=sum(filter(H4:H, AND(regexmatch(I4:I, "TextA"), regexmatch(J4:J, "TextB"))))

Also, filter allows for several conditions.

=sum(filter(H4:H, regexmatch(I4:I, "TextA"), regexmatch(J4:J, "TextB")))
  • In the past AND didn't work with arrays. Did you test it? – Rubén Mar 19 '17 at 15:40
  • @Rubén I must admit that I did not. – SQB Mar 19 '17 at 15:41
  • Google made changes that make that SPLIT work with arrays, so it will be reasonable to make a try with AND – Rubén Mar 19 '17 at 15:44

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.