If count

  • Thread starter Thread starter js20217075
  • Start date Start date
J

js20217075

start date end date
01/01/2010 01/01/2010
01/01/2010
01/01/2010
01/01/2010 01/01/2011
01/01/2010
01/01/2010
I want to count the start date column only if there is nothing in the end
date column.
 
The clearer the question, the faster the response, and vice versa. Things
that help are the version of Excel you are using, and where the data is
(column A, B, etc.). For this, I've assumed your start date is in A, and
your end date is in B.

For all entries in A, where there is no entry in B:
=sumproduct(--(a1:a1000<>""),--(b1: b1000=""))
Adjust the range to suit.

For specific entries in A, with no entry in B:
=sumproduct(--(a1:a1000=date(2010,1,1)),--(b1:b1000=""))

Regards,
Fred
 
Try one of these..

=SUMPRODUCT((A1:A100>0)*(B1:B100=0))

=SUMPRODUCT((ISNUMBER(A1:A100))*(ISBLANK(B1:B100)))
 
Apply filter on both the colums. On end date column remove tick mark
from end date column> Blanks on the status bar you can see the number
of cells being displayed. Else you can use =SUBTOTAL(3,A:A) in any of
the visible cells it would return the count.

A third option can be =COUNTA(A1:A100)-COUNTBLANK(B1:B100)

Hope that helps,
Anand
 
Back
Top