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

 

[Fluff]

Normally a formula would return a lower count rather than a higher one. If you remove both date references in the formula do you get the correct count?

 

[rfletcher35]

Normally a formula would return a lower count rather than a higher one. If you remove both date references in the formula do you get the correct count?

 

[rfletcher35]

If I remove the date and leave =COUNTIFS('Original Data'!$AJ:$AJ,EK4,'Original Data'!$F:$F,$CK$3,'Original Data'!$AO:$AO,"Excellent")

I get 343

 

[Fluff]

Is that the right count?

 

[rfletcher35]

If I remove the date and leave =COUNTIFS('Original Data'!$AJ:$AJ,EK4,'Original Data'!$F:$F,$CK$3,'Original Data'!$AO:$AO,"Excellent")

I get 343

Which the actual count is 179

 

[Fluff]

If you are getting the actual count via the autofilter, make sure that you don't have any other columns filtered.

 

[rfletcher35]

If you are getting the actual count via the autofilter, make sure that you don't have any other columns filtered.

Removed all filtering, turned it off and still get 82, does there look anything wrong in the formula, is there another formula to use to get to what i'm doing?

 

[Fluff]

What I meant was when getting the count of 179, are you sure you didn't have any other columns filtered?

 

[rfletcher35]

Which the actual count is 179

yes I must have had, it is now showing correct without the date in

 

[Fluff]

And with the date?