11

Columns A and B both have dates. If the date in column A is greater than the date in the same row of column B, the row should be counted. To add context, I am trying to count the number of rows where the completion date is past the due date. How can this be accomplished?

Thanks.

spassen
  • 1,550
  • 8
  • 20
  • 32

3 Answers3

16

You can try this:

=SUMPRODUCT(--(A:A>B:B))

It will sum all instances in the range where the date in column A is greater than that in column B.

You can also do this, however it needs to be entered as an array formula (Ctrl+Shift+Enter):

=SUM(IF(A:A>B:B,1,0))
RocketDonkey
  • 36,383
  • 7
  • 80
  • 84
1

Create a column that has a function to perform the comparison, e.g. =A1 > B1. This will give you a column that contains TRUE or FALSE for your comparison. Then you can use the COUNTIF function to count the results of this function, e.g. =COUNTIF(C:C, "=TRUE").

Fls'Zen
  • 4,594
  • 1
  • 29
  • 37
0

I would add a new additional column (say C) which should have this formula =IF(A1>B1; 1; 0). Then you can add a new cell =SUM(C:C) to get the result number.

Anton
  • 4,544
  • 2
  • 25
  • 31