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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,949
Office Version
  1. 365
Platform
  1. Windows
Is the formula giving a higher or lower count than filtering the data?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,949
Office Version
  1. 365
Platform
  1. Windows
I can think of no reason why the count would be too high.
Can you share the workbook?
 

rfletcher35

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

ADVERTISEMENT

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
 

rfletcher35

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

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,949
Office Version
  1. 365
Platform
  1. Windows
What if you just do a countif on the dates, does that give the right answer?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,886
Office Version
  1. 365
Platform
  1. Windows
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

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

Forum statistics

Threads
1,144,282
Messages
5,723,474
Members
422,499
Latest member
think say

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