YES or NO function with 3 criteria

cmacnab

Board Regular
Joined
Jun 24, 2013
Messages
57
Office Version
  1. 2016
Platform
  1. Windows
Hello, I'm trying to do a formula to match 3 different criteria to get a yes or no answer. It is not working out, possibly because several of the cells I am working with are already formulas.

The question is basic; is there a line on a specific date, for a specific job code for AM. This is the formula that I'm trying to use:
Cell Formulas
RangeFormula
A4A4=SUMMARY!$J$1
B4:B17B4=IF(AND('Punch Report'!$R:$R=$A4,'Punch Report'!$H:$H="BARTENDER",'Punch Report'!$W:$W="AM"),"YES","NO")
A5:A17A5=A4+1


This is the sample data that I am looking for the information in (Monday the response would be NO and the other 2 days YES):
PP11-2022 Reuben's (2020-05-16 to 2022-05-29).xlsx
ABHRW
1Employee IDDateRoleDate Shift
127CN6000000050Tue-17-May-22BARTENDER44698 AM
287CN6200000085Tue-17-May-22BARTENDER44698 PM
457CN6000000024Wed-18-May-22BARTENDER44699 AM
737CN6000000050Wed-18-May-22BARTENDER44699 AM
Punch Report
Cell Formulas
RangeFormula
R12,R73,R45,R28R12=LEFT(B12,5)
W12,W73,W45,W28W12=IF(V12<0.583333333333333,"AM",IF(V12>=1,"AM","PM"))


I have tried to refer to both column B and column R for the dates thinking that was causing the problem but same results regardless which of those 2 columns are referenced. Can someone assist with this? Thanks in advance.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
How about
Excel Formula:
=IF(COUNTIFS('Punch Report'!$R:$R,$A4,'Punch Report'!$H:$H,"BARTENDER",'Punch Report'!$W:$W,"AM"),"YES","NO")
 
Upvote 0
Solution
How about
Excel Formula:
=IF(COUNTIFS('Punch Report'!$R:$R,$A4,'Punch Report'!$H:$H,"BARTENDER",'Punch Report'!$W:$W,"AM"),"YES","NO")
Works beautifully! Thank you. Now I just have to figure out how to modify the time for double shifts, lol.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

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
Back
Top