# Countifs not counting correctly

#### rfletcher35

##### Active Member
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 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
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
=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
I would suspect EK4 is correct as it's relative, whereas CK3 is absolute.

#### Fluff

##### MrExcel MVP, Moderator

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

=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
What about my 1st comment in post#2?

#### rfletcher35

##### Active Member
What about my 1st comment in post#2?
Apolgies I meant to copy column AJ it has the same info that is imported, so it still says O'Connor, Jamie

#### rfletcher35

##### Active Member
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

Replies
4
Views
42
Replies
10
Views
198
Replies
5
Views
164
Replies
10
Views
189
Replies
4
Views
167

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.

### Which adblocker are you using?

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

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