Google Sheet textjoin as criterion in sumifs not working

Question

Hi I am trying to get the following formula to work unsuccessfully: =ARRAYFORMULA(sumifs(B1:B9,REGEXMATCH(A1:A9,""""&textjoin("|",1,E1:E2)&""""),TRUE,C1:C9,F1))

Cells E1=a E2=b

This formula gets the desired results: =ARRAYFORMULA(sumifs(B1:B9,regexmatch(A1:A9,"a|b"),true,C1:C9,F1))

and """"&TEXTjoin("|",1,E1:E8)&"""" results in "a|b"

Need some guidance please. Thanks.

a 1 nail a nail b 2 nail b
c 3 nail
a 10 hammer
b 11 hammer
c 12 hammer
a 20 nail
b 21 screw
c 22 screw

=ARRAYFORMULA(sumifs(B1:B9,REGEXMATCH(A1:A9,""""&textjoin("|",1,E1:E2)&""""),TRUE,C1:C9,F1)) results 0

=ARRAYFORMULA(sumifs(B1:B9,regexmatch(A1:A9,"a|b"),true,C1:C9,F1)) results 23

Answer 1

You may try:

=sumifs(B:B,C:C,F1,index(--regexmatch(A:A,textjoin("|",1,E:E))),1)

Updated formula:

=sumifs(B:B,C:C,F1,index(xmatch(A:A,E:E)^0),1)