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:
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
Here is the logic checklist I wanted to do and return the outcome:
- Staff A have 5 columns and each have 7 working days ranging C6:P6, C15:P15, C24:P24, C33:P33 & C42:P42
- 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
- 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.
- 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.
- 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.
- 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.
- 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
