Error With Countifs, function any help appreciated

Question

I am helping a friend to make a time sheet.

he has a time sheet where he logs his activities:

sleep poker study youtube, etc

and wanted to be able to count all the random activites that do not fall into those catagories

i came up with

=COUNTIFS(B18:C28,"<>poker",B18:C28,"<>study", B18:C28,"<>sleep", B18:C28,"<>watched y.tube", B18:C28,"<>")

which worked on my test sheet.

however he wants to apply this to multiple ranges in his sheet

so he for his sheet needed to use

=COUNTIFS( L4:R27,C31:I54,L31:R54,C58:E81,"<>poker", L4:R27,C31:I54,L31:R54,C58:E81,"<>studied", L4:R27,C31:I54,L31:R54,C58:E81,"<>sleep", L4:R27,C31:I54,L31:R54,C58:E81,"<>watched y.tube", L4:R27,C31:I54,L31:R54,C58:E81,"<>shower/eat" , L4:R27,C31:I54,L31:R54,C58:E81,"<>")

now we get an error saying

"ERROR" COUNTIFS expects all arguments after position 2 to be in pairs.

it seems to be counting the extra ranges as arguments.

i have tried to play with ARRAYFORMULA but this is now way above my skillset, so any help would be appreciated.

Answer 1

use:

=LAMBDA(x, COUNTA(FILTER(x, NOT(REGEXMATCH(""&x, 
 "(?i)poker|studied|sleep|watched|y.tube|shower\/eat"))))) 
 (FLATTEN({L4:R27, C31:I54, L31:R54, C58:E81}))

Answer 2

Try wrapping them with {} like this:

{L4:R27,C31:I54,L31:R54,C58:E81}

You can also make a LAMBDA function so you don't have to write your range again and again when you modify it:

=LAMBDA(range,COUNTIFS(range,"<>poker", range,"<>studied", range,"<>sleep", range,"<>watched y.tube", range,"<>shower/eat",range,"<>"))({L4:R27,C31:I54,L31:R54,C58:E81})

This way you can change only that last call with the ranges and the formula will update correctly