If count

[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.

 

[js20217075]

Please help! I only have 1/2 an hour to figure this out.

 

[Fred Smith]

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

 

[Jacob Skaria]

Try
=SUMPRODUCT((A1:A100<>"")*(B1:B100=""))

 

[js20217075]

This isn't working. OMG do you know anything else to try?

 

[Jacob Skaria]

Try one of these..

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

=SUMPRODUCT((ISNUMBER(A1:A100))*(ISBLANK(B1:B100)))

 

[Anand]

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