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

ahjeff86

New Member
Joined
Sep 26, 2018
Messages
15
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
    • 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.
  3. 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.
  4. 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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

mlast1991

New Member
Joined
Mar 12, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Good morning,

If I am assuming correctly you mean that there are 5 rows each with 7 days, also assuming that the cells are merged by two.
Please see the screenshot attached, I have used the formula and based it on cell values at S5, S6, S7 & T5, T6 and T7.

=IF(OR(C6=S5,C6=S6,C6=S7),IF(OR(C5=T5,C5=T6,C5=T7),0,1),1)
This could also be written as:

=IF(OR(C6="PH",C6="初一",C6="初二"),IF(OR(C5="OFF",C5="CLOSE",C5="AL"),0,1),1)

However, the first way would allow you to create a dropdown to make it easier.

Hope this helps,
Kind regards,
Mlast
 

Attachments

  • Screenshot.jpg
    Screenshot.jpg
    82.5 KB · Views: 3

ahjeff86

New Member
Joined
Sep 26, 2018
Messages
15
Good morning,

If I am assuming correctly you mean that there are 5 rows each with 7 days, also assuming that the cells are merged by two.
Please see the screenshot attached, I have used the formula and based it on cell values at S5, S6, S7 & T5, T6 and T7.

=IF(OR(C6=S5,C6=S6,C6=S7),IF(OR(C5=T5,C5=T6,C5=T7),0,1),1)
This could also be written as:

=IF(OR(C6="PH",C6="初一",C6="初二"),IF(OR(C5="OFF",C5="CLOSE",C5="AL"),0,1),1)

However, the first way would allow you to create a dropdown to make it easier.

Hope this helps,
Kind regards,
Mlast

thanks for your effort, due to forum couldn't upload actual file. I added into my dropbox and u can view at here:

please note the day "Mon","Tues" and so on, the word PH or 初一 or 初二 will be added manually by user according to the public holiday fall on the calendar.
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
@ ahjeff86
Your post has been reported as being cross posted with another site.

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Please provide the link(s)
 

mlast1991

New Member
Joined
Mar 12, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

For simplicity I would separate the day and the word on each one: G5=Wed then H5=PH etc.
Then use the formula I stated previously as:
=IF(OR(H5="PH",H5="初一",H5="初二"),IF(OR(G6="OFF",G6="CLOSE",G6="AL"),0,1),1)
Screenshot.jpg

Screenshot.jpg


I would copy the formula for each day and then sum the values for the week.
 

ahjeff86

New Member
Joined
Sep 26, 2018
Messages
15
@ ahjeff86
Your post has been reported as being cross posted with another site.

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Please provide the link(s)

@mole999

my apologize, as I couldn't edit the original post hence I add it at here.

The question above also asked in Excel Form: Formula to check range cell if match then check the cell value and return true or false


For simplicity I would separate the day and the word on each one: G5=Wed then H5=PH etc.
Then use the formula I stated previously as:
=IF(OR(H5="PH",H5="初一",H5="初二"),IF(OR(G6="OFF",G6="CLOSE",G6="AL"),0,1),1)View attachment 8908
View attachment 8909

I would copy the formula for each day and then sum the values for the week.

Hi @mlast1991,

Thanks for your suggestion, however I prefer all the code to run in S6 column for Staff A rather than hardcode everything.
 

ahjeff86

New Member
Joined
Sep 26, 2018
Messages
15
Since got no reply, I use the conventional way to achieve my desired result, the code as below:

VBA Code:
=SUM(IF(OR(ISNUMBER(SEARCH(CNY_PH,C5))),IF(OR(ISNUMBER(SEARCH(noOFF,C6))),0,1),0)+IF(OR(ISNUMBER(SEARCH(CNY_PH,E5))),IF(OR(ISNUMBER(SEARCH(noOFF,E6))),0,1),0)+IF(OR(ISNUMBER(SEARCH(CNY_PH,G5))),IF(OR(ISNUMBER(SEARCH(noOFF,G6))),0,1),0)+IF(OR(ISNUMBER(SEARCH(CNY_PH,I5))),IF(OR(ISNUMBER(SEARCH(noOFF,I6))),0,1),0)+IF(OR(ISNUMBER(SEARCH(CNY_PH,K5))),IF(OR(ISNUMBER(SEARCH(noOFF,K6))),0,1),0)+IF(OR(ISNUMBER(SEARCH(CNY_PH,M5))),IF(OR(ISNUMBER(SEARCH(noOFF,M6))),0,1),0)+IF(OR(ISNUMBER(SEARCH(CNY_PH,O5))),IF(OR(ISNUMBER(SEARCH(noOFF,O6))),0,1),0))+SUM(IF(OR(ISNUMBER(SEARCH(CNY_PH,C14))),IF(OR(ISNUMBER(SEARCH(noOFF,C15))),0,1),0)+IF(OR(ISNUMBER(SEARCH(CNY_PH,E14))),IF(OR(ISNUMBER(SEARCH(noOFF,E15))),0,1),0)+IF(OR(ISNUMBER(SEARCH(CNY_PH,G14))),IF(OR(ISNUMBER(SEARCH(noOFF,G15))),0,1),0)+IF(OR(ISNUMBER(SEARCH(CNY_PH,I14))),IF(OR(ISNUMBER(SEARCH(noOFF,I15))),0,1),0)+IF(OR(ISNUMBER(SEARCH(CNY_PH,K14))),IF(OR(ISNUMBER(SEARCH(noOFF,K15))),0,1),0)+IF(OR(ISNUMBER(SEARCH(CNY_PH,M14))),IF(OR(ISNUMBER(SEARCH(noOFF,M15))),0,1),0)+IF(OR(ISNUMBER(SEARCH(CNY_PH,O14))),IF(OR(ISNUMBER(SEARCH(noOFF,O15))),0,1),0))+SUM(IF(OR(ISNUMBER(SEARCH(CNY_PH,C23))),IF(OR(ISNUMBER(SEARCH(noOFF,C24))),0,1),0)+IF(OR(ISNUMBER(SEARCH(CNY_PH,E23))),IF(OR(ISNUMBER(SEARCH(noOFF,E24))),0,1),0)+IF(OR(ISNUMBER(SEARCH(CNY_PH,G23))),IF(OR(ISNUMBER(SEARCH(noOFF,G24))),0,1),0)+IF(OR(ISNUMBER(SEARCH(CNY_PH,I23))),IF(OR(ISNUMBER(SEARCH(noOFF,I24))),0,1),0)+IF(OR(ISNUMBER(SEARCH(CNY_PH,K23))),IF(OR(ISNUMBER(SEARCH(noOFF,K24))),0,1),0)+IF(OR(ISNUMBER(SEARCH(CNY_PH,M23))),IF(OR(ISNUMBER(SEARCH(noOFF,M24))),0,1),0)+IF(OR(ISNUMBER(SEARCH(CNY_PH,O23))),IF(OR(ISNUMBER(SEARCH(noOFF,O24))),0,1),0))+SUM(IF(OR(ISNUMBER(SEARCH(CNY_PH,C32))),IF(OR(ISNUMBER(SEARCH(noOFF,C33))),0,1),0)+IF(OR(ISNUMBER(SEARCH(CNY_PH,E32))),IF(OR(ISNUMBER(SEARCH(noOFF,E33))),0,1),0)+IF(OR(ISNUMBER(SEARCH(CNY_PH,G32))),IF(OR(ISNUMBER(SEARCH(noOFF,G33))),0,1),0)+IF(OR(ISNUMBER(SEARCH(CNY_PH,I32))),IF(OR(ISNUMBER(SEARCH(noOFF,I33))),0,1),0)+IF(OR(ISNUMBER(SEARCH(CNY_PH,K32))),IF(OR(ISNUMBER(SEARCH(noOFF,K33))),0,1),0)+IF(OR(ISNUMBER(SEARCH(CNY_PH,M32))),IF(OR(ISNUMBER(SEARCH(noOFF,M33))),0,1),0)+IF(OR(ISNUMBER(SEARCH(CNY_PH,O32))),IF(OR(ISNUMBER(SEARCH(noOFF,O33))),0,1),0))+SUM(IF(OR(ISNUMBER(SEARCH(CNY_PH,C41))),IF(OR(ISNUMBER(SEARCH(noOFF,C42))),0,1),0)+IF(OR(ISNUMBER(SEARCH(CNY_PH,E41))),IF(OR(ISNUMBER(SEARCH(noOFF,E42))),0,1),0)+IF(OR(ISNUMBER(SEARCH(CNY_PH,G41))),IF(OR(ISNUMBER(SEARCH(noOFF,G42))),0,1),0)+IF(OR(ISNUMBER(SEARCH(CNY_PH,I41))),IF(OR(ISNUMBER(SEARCH(noOFF,I42))),0,1),0)+IF(OR(ISNUMBER(SEARCH(CNY_PH,K41))),IF(OR(ISNUMBER(SEARCH(noOFF,K42))),0,1),0)+IF(OR(ISNUMBER(SEARCH(CNY_PH,M41))),IF(OR(ISNUMBER(SEARCH(noOFF,M42))),0,1),0)+IF(OR(ISNUMBER(SEARCH(CNY_PH,O41))),IF(OR(ISNUMBER(SEARCH(noOFF,O42))),0,1),0))

CNY_PH is the name range I set for the criteria while noOFF is the name range I set for the cross check.

Any simplified method than above are most welcome, else Mods please close the thread. thanks
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
@ahjeff86 threads are never closed (odd exception) or maked as completed, and as this is a site where 24 hours sees people from all over the world vist there may yet be answers to add later
 

Watch MrExcel Video

Forum statistics

Threads
1,128,165
Messages
5,629,068
Members
416,363
Latest member
zaveedd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top