# Countifs with dates

#### becka

##### New Member
Hi,

I have a formula which isn't quite working (or, more accurately, I think there might be something wrong with my data).

I'm trying to count dates in column I if they are later than the date in a corresponding row in column E.

I've used the following formula:

=COUNTIF(I75:I92, ">"&E75:E92)

which gives a count of 3.

However... Because I'm performing this on a small data set at the moment (I'm extending it later), I can perform a manual count and see that it should be 2. I've also done a check by adding a column and using the following formula in rows 75-92 to make sure I'm not missing anything in the manual count:

=IF(I75>E75, "Yes", "No")
=IF(I76>E76, "Yes", "No")
.
.
. etc
=IF(I92>E72, "Yes", "No")

which also gives two "yes" results, agreeing with my manual count. I've checked the values of each of the cells by using a VALUE formula to make sure that there's not anything weird with the formatting which e.g. makes it look like a date when it's text.

I don't understand why my COUNTIF isn't giving me the correct result- can anyone help? I've run out of ideas as to how to check what's going on. I'm sure there's probably something simple I'm missing, but can't see it at the moment!

Last edited:

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### bbott

##### Well-known Member
Welcome to the board.

COUNTIF can't be used like that. Try this instead:

=SUMPRODUCT(--(I75:I92>E75:E92))

#### becka

##### New Member
Thanks for the reply. Could you explain why I can't use COUNTIF, and what my COUNTIF formula is doing if it's not doing the thing I want it to?

#### bbott

##### Well-known Member
Thanks for the reply. Could you explain why I can't use COUNTIF, and what my COUNTIF formula is doing if it's not doing the thing I want it to?

Sure.

The COUNTIF function can't handle an array like that, so it only evaluates the the first value (E75). Basically, you're getting the count of how many cells in I75:I92 are greater than E75.

The SUMPRODUCT function compares the two arrays line by line and sums up the number of values in I75:I92 that are greater than E75:E92.

#### becka

##### New Member
Brilliant, thanks!

Replies
3
Views
471
Replies
4
Views
277
Replies
1
Views
347
Replies
1
Views
1K
Replies
2
Views
795

1,195,582
Messages
6,010,581
Members
441,557
Latest member
Jbest23

### 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?

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