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.

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

try this, it should count all the values inside the given range which doesn't match the given string.

You can change the RANGE to check and the list of STRING to ignore at the last 2 lines of the code.

What the code doing is:

1. with LAMBDA function, name the range to check as RANGES,
2. with LAMBDA function, name the activities to ignore as ACTIVITIES,
3. with LAMBDA function, name the flattened data draw from RANGES as DATA,
4. with IF function, convert any Numbers inside DATA to string,
5. the reason why we need step 4, is because QUERY function do not handle mixed data type well,
6. with QUERY function, filter all values of DATA to find non-matches with ACTIVITIES,
7. with COUNTA, count the number of values returned by the QUERY in step 6.

=ArrayFormula(
 LAMBDA(RANGES,ACTIVITIES,
  LAMBDA(DATA,
   COUNTA(QUERY(IF(ISNUMBER(DATA),TO_TEXT(DATA),DATA)," WHERE "&JOIN(" AND ","Col1<>'"&ACTIVITIES&"'"),0))
  )(FILTER(FLATTEN(RANGES),ISBLANK(FLATTEN(RANGES))=FALSE))
 )(
   {L4:R27,C31:I54,L31:R54,C58:E81},
   {"poker","sleep","studied","watched y.tube","shower/eat"}
  )
)