Conditional format a cell based on values in a dataset column and dates in another column

Rule for green:

=COUNTIFS(mthRange,">=01/01/2022",mthRange,"<=31/01/2022")-COUNTIFS(mthRange,">=01/01/2022",mthRange,"<=31/01/2022",PFRange,"PASS")=0

Rule for red:

=COUNTIFS(mthRange,">=01/01/2022",mthRange,"<=31/01/2022")-COUNTIFS(mthRange,">=01/01/2022",mthRange,"<=31/01/2022",PFRange,"FAIL")=0

Rule for amber:

=COUNTIFS(mthRange,">=01/01/2022",mthRange,"<=31/01/2022")-COUNTIFS(mthRange,">=01/01/2022",mthRange,"<=31/01/2022",PFRange,"PASS")<>0

Hi Ali
That worked very well. Thank you. The only issue is that for some reason the formula does not recognise the date in a dataset returned by PowerQuery as date. I know this is a separate issue. My query has select convert(varchar, DATE, 3) so i would expect it to return date as General when i click Format Cell.
Please see attached picture. The date in the green cell (returned from PowerQuery) is not recognised as a "Date' while the one in white is.
Attachment 776389date.PNG

Selecting attachment 776389 results in the following message: "Invalid Attachment specified. If you followed a valid link, please notify the administrator".
It may be better to utilize the information in the banner at the top of the page to upload an Excel file as was done in post #1.
Let us know if you have any questions.

I can't replicate the issue.
In the attached file the data in columns A:B is converted to an Excel table and then used to produce the Power Query table in columns D:E.
The formulas on the summary sheet and the conditional formatting applied to cell B2 on that sheet recognize the dates in the Power Query table.
Perhaps uploading a file that illustrates the issue would help.
Let us know if you have any questions.

It appears that the table on the Data2 sheet is an Excel table and not produced using power query.
The data in cell J2 is text. In order to convert to a date:
1. Select cell J2
2. From the Data tab on the ribbon at the top select Text to Columns
3. Select Next and Next
4. Select Date and then change the drop down to DMY
5. Select Finish
Cell B11 on the Summary sheet should now display 1
Note that in step 1 you could select J2:J1000, however you can only select one column at a time so you will need to repeat steps 1:5 with K2:K1000 ... N2:N1000
Let us know if you have any questions.

If you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.