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 !