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]

And with the date?

Shows incorrect

 

[Fluff]

Is the formula giving a higher or lower count than filtering the data?

 

[rfletcher35]

Is the formula giving a higher or lower count than filtering the data?

Higher 82 instead of the correct figure of 32

 

[Fluff]

I can think of no reason why the count would be too high.
Can you share the workbook?

 

[rfletcher35]

I can think of no reason why the count would be too high.
Can you share the workbook?

Can't really because of the data it holds, I could let you jump on my system with quick assist if that is allowed on here?
Ive just tried something different by referencing a date range on another sheet instead but got #spill error

 

[Fluff]

I could let you jump on my system with quick assist if that is allowed on here?

Unfortunately not.

 

[rfletcher35]

Unfortunately not.

It has to be something that it doesn't like with the date range so just need a work around with that

 

[Fluff]

What if you just do a countif on the dates, does that give the right answer?

 

[jasonb75]

Do the dates in the formula (and the data) match your regional settings? Looking at your image, it appears that the dates are valid based on the right alignment within the cells but that is not always a guaranteed factor.

A quick test would be =COUNT(AB:AB) and =COUNTA(AB:AB)
If there is nothing else in that column other than the dates and the column header then the result of the second formula should be 1 greater than the first, anything else suggests a problem with the dates.

 

[rfletcher35]

Do the dates in the formula (and the data) match your regional settings? Looking at your image, it appears that the dates are valid based on the right alignment within the cells but that is not always a guaranteed factor.

A quick test would be =COUNT(AB:AB) and =COUNTA(AB:AB)
If there is nothing else in that column other than the dates and the column header then the result of the second formula should be 1 greater than the first, anything else suggests a problem with the dates.

It reads 1 greater than the first as you said