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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,074
Office Version
  1. 365
Platform
  1. Windows
No idea if this is the problem, but you formula is looking at col AJ, whilst your image is showing col M
 

Sufiyan97

Well-known Member
Joined
Apr 12, 2019
Messages
539
Office Version
  1. 2013
Platform
  1. Windows
=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")

is above reference is correct?
or it sholuld be

Excel Formula:
=COUNTIFS('Original Data'!$AJ:$AJ,EK3,'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")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,074
Office Version
  1. 365
Platform
  1. Windows
I would suspect EK4 is correct as it's relative, whereas CK3 is absolute.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,074
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Another thing, as col AB has both date & time your formula should be
Excel Formula:
=COUNTIFS('Original Data'!$AJ:$AJ,EK4,'Original Data'!$F:$F,$CK$3,'Original Data'!$AB:$AB,">=1/5/2021",'Original Data'!$AB:$AB,"<1/6/2021",'Original Data'!$AO:$AO,"Excellent")
otherwise it won't count any rows with a date of 31st May
 

rfletcher35

Active Member
Joined
Jul 20, 2011
Messages
282
Office Version
  1. 365
Platform
  1. Windows
=COUNTIFS('Original Data'!$AJ:$AJ,EK4,'Original Data'!$F:$F,$CK$3,'Original Data'!$AB:$AB,">=1/5/2021",'Original Data'!$AB:$AB,"<1/6/2021",'Original Data'!$AO:$AO,"Excellent")
Tried this, it still reports 82
 

rfletcher35

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

ADVERTISEMENT

=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")

is above reference is correct?
or it sholuld be

Excel Formula:
=COUNTIFS('Original Data'!$AJ:$AJ,EK3,'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")
No for that line it should reference EK4
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,074
Office Version
  1. 365
Platform
  1. Windows
What about my 1st comment in post#2?
 

rfletcher35

Active Member
Joined
Jul 20, 2011
Messages
282
Office Version
  1. 365
Platform
  1. Windows
No for that line it should reference EK4
EK4 refers to the name of the person, EK3 has another name in so does EK5,6,7 I chose chose that one as it stands out so much, I just cannot figure out why the countif is reporting 82 when it clearly isn't
 

Forum statistics

Threads
1,148,417
Messages
5,746,553
Members
424,032
Latest member
pochie2741

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