COUNTIFS with logical AND and advanced criteria

Question

Hi stack exchange community !

I'm trying to make a presence board to keep track of available & spent hours. I linked a demo sheet at the end of this post.

The "Config" tab defines roles and assigns a number of available hours to each person depending on their roles.

The "January" tab is a calendar where everyone can input their working hours (M, A or J which stand respectively for morning, afternoon and whole day (actually i'm french, M=matin, A=après-midi & J=journée))

Sometimes, we have meetings which should not count as working time, that's what the G column is for :

• when it's set to "J", no time is spent for those who input on that day
• when it's set to "M", time will be spent only for people who worked on the afternoon
• when it's set to "A", time will be spent only for people who worked on the morning

Everything is working well so far. What i'm trying to do is add several types of meetings associated with each role : sometimes, meetings only implicate RP people, or CSSCT people.

When such a meeting happen, only the working time of people who DON'T have the associated role should increase. For instance, if RP meeting happens :

• RP people who input time for that meeting SHOULD NOT see their spent time increase
• CSSCT people may input working time during the meeting (meaning they work on their own but at the same time) and they SHOULD see their spent time increase

As you can see, much of the magic happens at line 38 with a big fat formula that I can't seem to tweak to do what I just described above. Some of the things I've tried so far are :

• script : I simply can't have a sheet that does not update itself instantaneously
• use logical AND inside COUNTIFS function to handle complex conditions, that does not seem to work
• use IF function inside COUNTIFS but that does not seem to work either
• I've searched a lot of forums but can't seem to find anything relevant to my use case (Arrayformula, query...)

Does anyone have insight on what might interest me to solve that problem ?

Thanks in advance !

The demo sheet : https://docs.google.com/spreadsheets/d/1PY7pT4CrvtWEhZ64CGRKEItZV1W7vxBEiIpLX8LlnSU/edit?usp=sharing

Answer 1

After a few hours, I finally figured out I had to completely change my thinking paradigm as COUNTIFS didn't seem appropriate. My solution was to replace it with SUM(ARRAYFORMULA()) and use an IF inside, expressing the complex conditions I needed on a range (instead of single cells) as follows :

if (A and (B or C)) then value1 else value2 --> IF (A * (B + C) ; value1 ; value2)

Inside an IF, the boolean condition A AND B can be expressed as A * B instead of AND(A;B). Likewise, one can write A + B instead of OR(A;B).

Moreover, IFs can be nested so that multiple elsif cases can be handled, allowing one to express complex conditions.

For those who would like to see the actual formulas, this is what I struggled with :

=MROUND(

 0,5*duree_journee*COUNTIFS(FILTER(B4:B34;NOT(WEEKDAY($A4:$A34)=6));"M";FILTER($G4:$G34;NOT(WEEKDAY($A4:$A34)=6));"<>M";FILTER($G4:$G34;NOT(WEEKDAY($A4:$A34)=6));"<>J")
+0,5*duree_journee*COUNTIFS(FILTER(B4:B34;NOT(WEEKDAY($A4:$A34)=6));"A";FILTER($G4:$G34;NOT(WEEKDAY($A4:$A34)=6));"<>A";FILTER($G4:$G34;NOT(WEEKDAY($A4:$A34)=6));"<>J")
+0,5*duree_journee*COUNTIFS(FILTER(B4:B34;NOT(WEEKDAY($A4:$A34)=6));"J";FILTER($G4:$G34;NOT(WEEKDAY($A4:$A34)=6));"<>J";FILTER($G4:$G34;NOT(WEEKDAY($A4:$A34)=6));"<>")
+    duree_journee*COUNTIFS(FILTER(B4:B34;NOT(WEEKDAY($A4:$A34)=6));"J";FILTER($G4:$G34;NOT(WEEKDAY($A4:$A34)=6));"")

+0,5*duree_journee_vendredi*COUNTIFS(FILTER(B4:B34;WEEKDAY($A4:$A34)=6);"M";FILTER($G4:$G34;WEEKDAY($A4:$A34)=6);"<>M";FILTER($G4:$G34;WEEKDAY($A4:$A34)=6);"<>J")
+0,5*duree_journee_vendredi*COUNTIFS(FILTER(B4:B34;WEEKDAY($A4:$A34)=6);"A";FILTER($G4:$G34;WEEKDAY($A4:$A34)=6);"<>A";FILTER($G4:$G34;WEEKDAY($A4:$A34)=6);"<>J")
+0,5*duree_journee_vendredi*COUNTIFS(FILTER(B4:B34;WEEKDAY($A4:$A34)=6);"J";FILTER($G4:$G34;WEEKDAY($A4:$A34)=6);"<>J";FILTER($G4:$G34;WEEKDAY($A4:$A34)=6);"<>")
+    duree_journee_vendredi*COUNTIFS(FILTER(B4:B34;WEEKDAY($A4:$A34)=6);"J";FILTER($G4:$G34;WEEKDAY($A4:$A34)=6);"")

;arrondi_heures_consommees)

And this is what my solution looks like (which is actually more complex but way shorter) :

=MROUND(SUM(ARRAYFORMULA(
    IF(WEEKDAY($A4:$A34;2)<5;duree_journee;IF(WEEKDAY($A4:$A34;2)=5;duree_journee_vendredi;0))
    *IF(
        REGEXMATCH(B4:B34;"M")*(NOT(REGEXMATCH($R4:$R34;"Plénière M|J"))+NOT(Config!B4))*(NOT(REGEXMATCH($R4:$R34;"CSSCT M|J"))+NOT(Config!B6))*(NOT(REGEXMATCH($R4:$R34;"RP M|J"))+NOT(Config!B7))
        +REGEXMATCH(B4:B34;"A")*(NOT(REGEXMATCH($R4:$R34;"Plénière A|J"))+NOT(Config!B4))*(NOT(REGEXMATCH($R4:$R34;"CSSCT A|J"))+NOT(Config!B6))*(NOT(REGEXMATCH($R4:$R34;"RP A|J"))+NOT(Config!B7))
        +REGEXMATCH(B4:B34;"J")*(REGEXMATCH($R4:$R34;"Plénière A|M")*Config!B4+REGEXMATCH($R4:$R34;"CSSCT A|M")*Config!B6+REGEXMATCH($R4:$R34;"RP A|M")*Config!B7);0,5;
            IF((B4:B34="J")*(NOT($R4:$R34="Plénière J")+NOT(Config!B4))*(NOT($R4:$R34="CSSCT J")+NOT(Config!B6))*(NOT($R4:$R34="RP J")+NOT(Config!B7));1;
                0))
));arrondi_heures_consommees)