Countifs with dates

[becka]

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!

Thanks in advance!

 

[bbott]

Welcome to the board.

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

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

 

[becka]

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]

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]

Brilliant, thanks!

 

[bbott]

Brilliant, thanks!

You're welcome.