Formula to check range cell if match then check the cell value and return true or false

Hi there as per title I would like to run through below under a single cell. However I couldn't figure out which direction should I go to get the exact outcome.

Here is the logic checklist I wanted to do and return the outcome:
1) Staff A have 5 columns and each have 7 working days ranging C6:P6, C15:P15, C24:P24, C33:P33 & C42:P42

2) The code need if these range of row C6:P6, C15:P15, C24:P24, C33:P33 & C42:P42 contains the word "PH" or "初一" or "初二" if match any of the range then it need to check the 5 row under Staff A row which is C5:P5, C14:P14, C23:P23, C32:P32 & C41:P41

3a) As my attached example worksheet: G5 column contain the word "PH" then the formula need to check Staff A within the same column which is G6 does it have any words contain "Off" or "Close" or "AL", if contain any of the word then it should return value of 0 else it return the value of 1, in this case column G6 doesn't contain any of the words hence it should return 1.
3b) Next check found the cell of M32 contain the word "初一", therefore the code need to check Staff A under the cell of M33 contains any of these word "Off" or "Close" or "AL", if contain any of the word then it should return value of 0 else it return the value of 1, in this case column M33 contain the word "Close" hence it should return 0.
3c) Next check found the cell of O32 contain the word "初二", therefore the code need to check Staff A under the cell of O33 contains any of these word "Off" or "Close" or "AL", if contain any of the word then it should return value of 0 else it return the value of 1, in this case column O33 doesn't contain any of the words hence it should return 1.

4) After checking all 5 rows for Staff A, it need to Sum up the finding, in this case the finding found 1+0+1 hence the answer 2 should be at the cell S6.

5) These method of checking need to apply to Staff B, Staff C, Staff D, Staff E & Staff F.

I thinking of vlookup but it doesn't seems to work with the logic and direction I want hence I need expert advice is it possible for me to achieve above result without using VBA Macro coding?

Thank you for your time

this question also asked by me in mrexcel forum: https://www.mrexcel.com/board/thread...false.1127452/

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

Administrative Note:

Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.

Originally Posted by Pepe Le Mokko

Administrative Note:

Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.

Your Administrative Note particularly the bold part doesn't sounds polite to me.

Please do read my reply on above I wrote "Since got no reply, I use the conventional way to achieve my desired result, the code as below:" and I even paste the code in so that it will benefit other users in future should they meet the similar issues with what I encounter.

Please note that I don't simply bump the thread and said "When I only can get an answer for the problem which I face now?" or wrote my reply as "Since no one bother to help, I use the conventional way to achieve my desired result, the code as below:", if I write this kind of reply I got no issue with your Administrative Note remarks.