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.
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}))