Countifs not counting correctly

rfletcher35

Active Member
Joined
Jul 20, 2011
Messages
282
Office Version
  1. 365
Platform
  1. Windows
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

Excel F.png
Excel M.png
Excel AB.png
Excel AO.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,995
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

rfletcher35

Active Member
Joined
Jul 20, 2011
Messages
282
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,995
Office Version
  1. 365
Platform
  1. Windows
Is that the right count?
 

rfletcher35

Active Member
Joined
Jul 20, 2011
Messages
282
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,995
Office Version
  1. 365
Platform
  1. Windows
If you are getting the actual count via the autofilter, make sure that you don't have any other columns filtered.
 

rfletcher35

Active Member
Joined
Jul 20, 2011
Messages
282
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,995
Office Version
  1. 365
Platform
  1. Windows
What I meant was when getting the count of 179, are you sure you didn't have any other columns filtered?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,995
Office Version
  1. 365
Platform
  1. Windows
And with the date?
 

Forum statistics

Threads
1,144,422
Messages
5,724,227
Members
422,543
Latest member
Bravo661

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top