Countifs not counting correctly

[rfletcher35]

Hi Guys, I have a problem with the formula below in that it does not seem to be counting correctly

=COUNTIFS('Original Data'!$AJ:$AJ,EK4,'Original Data'!$F:$F,$CK$3,'Original Data'!$AB:$AB,">=1/5/2021",'Original Data'!$AB:$AB,"<=31/5/2021",'Original Data'!$AO:$AO,"Excellent")

The cell that the above code is in reports 82, but the actual figure using a manual filter search is 32, I've added pics of the columns it is looking at below, this is what I get when doing a manual filter

EK4 is simply O'Connor, Jamie & CK3 is Erevena Ltd (IT)

Can anyone explain what is going wrong please i'm lost

Thanks

Fletch

 

[rfletcher35]

It reads 1 greater than the first as you said

Also tried changing formula to
=COUNTIFS('Original Data'!$AJ:$AJ,EK4,'Original Data'!$F:$F,$CK$3,'Original Data'!$AB:$AB,"=DATES!E2:E32",'Original Data'!$AO:$AO,"Excellent")
where I have created a tab called DATES and column E2:E32 has all the dates in May but this returns 0

 

[rfletcher35]

It reads 1 greater than the first as you said

Also tried naming the range but cannot fugure out how to implement his into my formula to see if this works around the problem

 

[Fluff]

The formula in post#31 will not work because col AB contains date & time, so they will never equal just a date.

 

[jasonb75]

With everything that has been established so far, there is absolutely no reason why the original formula would return an incorrect count.

Last guess is that maybe there are some hidden (not filtered) rows meeting the criteria.

If that is not the problem then try copying the data to a new workbook and testing the formula there.