COUNTIFS formula not working

[Jyggalag]

Hi all!

I am currently comparing values in two sheets to each other to see if they are similar or dissimilar.

I have this setup at the moment:

My formula is: =IF(COUNTIFS('[CONFIDENTIAL - NUMBER.xlsx]Sheet1'!$T$2:$T$1712;E2;'[CONFIDENTIAL - NUMBER.xlsx]Sheet1'!$R$2:$R$1712;A2)=1;"yes";"no")

I refer to this sheet to see if the value is also located there:

As you can see, the date is also there, and so is the amount.

Please note that I realize that the date is written differently, HOWEVER when I click on the cell, it is written like this in the formula bar:

I have also done individual checks by going IF(A2=R2;"YES";"NO") and IF(E2=T2;"YES";"NO") and they both return "YES". So the values are indeed identical in the two sheets, but for some reason my IF(COUNTIFS) formula is not working.

I have tried to rewrite my formula 10 times and have used it successfully in the past, so I truly do not understand what is going on and why it is returning "No" for me?

Can somebody please help? I believe that @RoryA helped me get this formula originally, so would truly appreciate his assistance as well

Thank you all!

Kind regards,
Jyggalag

 

[Fluff]

What happens if you remove the =1 from the end of the countifs formula

 

[Jyggalag]

Update: I tried making the formula but where i ONLY marked the necessary cells:

Now it returns "Yes". However, please note that I obviously need to mark the entire area in the other sheet and not just the cells in row 1075. No idea why this is happening?? So weird!

 

[Jyggalag]

Th

What happens if you remove the =1 from the end of the countifs formula

This worked!!!

Thank you so much Fluff!

Wow. Silly me haha

I guess it was because it might find the date 01-06-2022 and value 3000 two times or more, which makes the count > 1

Didn't think about that!

Thank you! You've made my day A LOT easier

 

[ExceLoki]

try this

=IF(COUNTIFS('[CONFIDENTIAL - NUMBER.xlsx]Sheet1'!$T$2:$T$1712,E2,'[CONFIDENTIAL - NUMBER.xlsx]Sheet1'!$R$2:$R$1712,A2)=1,"yes","no")

 

[Fluff]

I guess it was because it might find the date 01-06-2022 and value 3000 two times or more, which makes the count > 1

That's right

Glad to help & thanks for the feedback.