7

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.

2 Answers 2

8

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

2
  • Any idea what are the performance differences between sumifs() and sum(filter())?
    – Yaron
    Apr 20, 2019 at 9:35
  • 1
    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, 2019 at 11:01
1

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")))
3
  • In the past AND didn't work with arrays. Did you test it?
    – Rubén
    Mar 19, 2017 at 15:40
  • @Rubén I must admit that I did not.
    – SQB
    Mar 19, 2017 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, 2017 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.