If count
 

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.

If count
 

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

"js20217075" wrote:

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.

If count
 

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




"js20217075" wrote in message
...
Please help! I only have 1/2 an hour to figure this out.

"js20217075" wrote:

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.

If count
 

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

--
Jacob


"js20217075" wrote:

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.

If count
 

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

"Jacob Skaria" wrote:

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

--
Jacob


"js20217075" wrote:

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.

If count
 

Try one of these..

=SUMPRODUCT((A1:A1000)*(B1:B100=0))

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


--
Jacob


"js20217075" wrote:

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

"Jacob Skaria" wrote:

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

--
Jacob


"js20217075" wrote:

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.

If count
 

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

On Feb 9, 9:11*am, js20217075
wrote:
This isn't working. *OMG do you know anything else to try?



"Jacob Skaria" wrote:
Try
=SUMPRODUCT((A1:A100<"")*(B1:B100=""))

--
Jacob

"js20217075" wrote:

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.- Hide quoted text -

- Show quoted text -