Google sheets - Count if column A text includes keywords from column B AND C

Question

In Google sheets, I have three columns (as set out below) each cell in column A has a block of text in. Column B is a set of keywords on coronavirus and column C is a set of keywords around data.

My goal is to find how many blocks of text talk about coronavirus statistics. I want to do that by counting how many cells in column A (text) contain any keyword from column B (coronavirus keywords) AND any keyword from column C (data keywords).

text	coronavirus keywords	data keywords
Yadda Yadda coronavirus yadda statistics	coronavirus	data
Yadda covid yadda	covid	statistics
Yadda covid statistics yadda yadda	COVID-19	stats
yadda yadda yadda	virus	trends
yadda yadda yadda yadda	illness
COVID-19 trends yadda yadda
covid yadda yadda stats

I've been trying with:

=ArrayFormula(SUM(COUNTIFS(A:A,B:B,A:A,C:C)))

Which produces an answer but I know it's wrong - it comes out as '1' but I know there's at least 5 matches.

Is there a way I can get this count?

Sorry, I can't share the actual sheets - work info.

Thanks!

Answer 1

try:

=INDEX(SUM(N(REGEXMATCH(A:A, "(?i)"&TEXTJOIN("|", 1, B2:C)))))

Answer 2

If an 'AND' function between the two lists of keywords is required (and possibly case insensitive match), I suggest

=SUMPRODUCT(REGEXMATCH(A:A, "(?i)"&TEXTJOIN("|", 1, B2:B))*REGEXMATCH(A:A, "(?i)"&TEXTJOIN("|", 1, C2:C)))