Countif Tasks are completed late?

Johnny Rox

Hey There~

I'm trying to count how many of my tasks are being completed late and I'm struggling to do this.

Here's what I got:

Here is the formula:
=COUNTIF([Completion Dates]:[Completion Dates], >[Due Dates]:[Due Dates])

This returns zero but I want it to return three. However, =COUNTIF([Completion Dates]1, >[Due Dates]1) returns one.

I've tried variations of @cell but still no dice.

#SOS

Screen Shot 2016-11-14 at 11.39.35 AM.png

Find more posts tagged with

Formulas

Comments

Johnny Rox

Hey Andrew,

That's true, but I have a pretty large sheet and I'm trying to reduce the number of assisting columns.

Thanks~

Andrew DeCounter

Alternative solution:

Create a checkbox column where to identify if a row's Completion Date > Due Date. Then, count that column to get your result.

=IF([Completion Dates]1 > [Due Dates]1, 1)

=COUNTIF([Past Due]:[Past Due], 1)

J. Craig Williams

Johnny,

I'm pretty sure this (an additional column) is not one you can avoid.

The COUNTIF or COUNTIFS functions allow ONE cell reference, not a matching one is another column of the same size.

There is no OFFSET function, so even trying to use the @cell reference for one column to the right, for example, won't work either. I (like you) tried various things with @cell and came up empty.

There is no way to "store" a value in the formula and use it later in the same formula.