I am trying to write an Countif statement that I will eventually turn into an
array. However, I am having problems with the dates. My statement now is:

Countif(A1:A100,"<=B1")

In this statement A1:A100 are all dates, and so is B1. The function just
gives me zero. However if I change the statement to:

Countif(A1:A100,"<=1/1/2009")

Then the statement works. If I physically type in the date it works fine,
but when I try to have it look up the date from another cell it doesn't work.
Any ideas?

Ulimately I want the equation to be

{=Count(if((A1:A100,"<=B1")*(C1:C100,"=B1),(D1:D1 00))}

I need to get past the first error before I can proceed.

If you want to use a cell reference in your COUNTIF statement, then it needs
to be outside of the quotes. Like this:

=COUNTIF(A1:A100,"<="&B1)

However, for your end result, I'd use SUMPRODUCT instead:

=SUMPRODUCT(--(A1:A100<=B1),--(C1:C100=B1),D1:D100)

HTH
Elkar


"Thebaran" wrote:

I am trying to write an Countif statement that I will eventually turn into an
array. However, I am having problems with the dates. My statement now is:

Countif(A1:A100,"<=B1")

In this statement A1:A100 are all dates, and so is B1. The function just
gives me zero. However if I change the statement to:

Countif(A1:A100,"<=1/1/2009")

Then the statement works. If I physically type in the date it works fine,
but when I try to have it look up the date from another cell it doesn't work.
Any ideas?

Ulimately I want the equation to be

{=Count(if((A1:A100,"<=B1")*(C1:C100,"=B1),(D1:D1 00))}

I need to get past the first error before I can proceed.

Try it like this:

=COUNTIF(A1:A100,"<="&B1)

{=Count(if((A1:A100,"<=B1")*(C1:C100,"=B1),(D1:D 100))}

Not sure what you want to do with that.


--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
I am trying to write an Countif statement that I will eventually turn into
an
array. However, I am having problems with the dates. My statement now
is:

Countif(A1:A100,"<=B1")

In this statement A1:A100 are all dates, and so is B1. The function just
gives me zero. However if I change the statement to:

Countif(A1:A100,"<=1/1/2009")

Then the statement works. If I physically type in the date it works fine,
but when I try to have it look up the date from another cell it doesn't
work.
Any ideas?

Ulimately I want the equation to be

{=Count(if((A1:A100,"<=B1")*(C1:C100,"=B1),(D1:D1 00))}

I need to get past the first error before I can proceed.

Hi,

The correct syntax for the first is

=COUNTIF(A1:A100,"<="&B1)

I don't understand what you are trying to do with the second formula,
perhaps you could explain.

Mike
"Thebaran" wrote:

I am trying to write an Countif statement that I will eventually turn into an
array. However, I am having problems with the dates. My statement now is:

Countif(A1:A100,"<=B1")

In this statement A1:A100 are all dates, and so is B1. The function just
gives me zero. However if I change the statement to:

Countif(A1:A100,"<=1/1/2009")

Then the statement works. If I physically type in the date it works fine,
but when I try to have it look up the date from another cell it doesn't work.
Any ideas?

Ulimately I want the equation to be



I need to get past the first error before I can proceed.

Countif(A1:A100,"<=B1")

should be
=COUNTIF(A1:A100,"<="&B1)

Countif(A1:A100,"<=1/1/2009")
I would use the DATE function:
=COUNTIF(A1:A100,"<="&DATE(2009,1,1))

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Tue, 3 Feb 2009 11:04:13 -0800, Thebaran
wrote:

I am trying to write an Countif statement that I will eventually turn into an
array. However, I am having problems with the dates. My statement now is:

Countif(A1:A100,"<=B1")

In this statement A1:A100 are all dates, and so is B1. The function just
gives me zero. However if I change the statement to:

Countif(A1:A100,"<=1/1/2009")

Then the statement works. If I physically type in the date it works fine,
but when I try to have it look up the date from another cell it doesn't work.
Any ideas?

Ulimately I want the equation to be

{=Count(if((A1:A100,"<=B1")*(C1:C100,"=B1),(D1:D 100))}

I need to get past the first error before I can proceed.

I have a lot of data for some items of work. The items can have either a
status of open, repaired, closed, or rejected. I need to determine the
number of each on any given day.

So if an item was opened on 12/1/08, repaired on 12/10/08 and closed on
12/20/08, then I want to be able to right an equation that will say on
12/15/08 we had 1 item that was repaired. As of right now I have over 1,500
activities. I just need to know how many open, repaired, closed, or rejected
items on any day.

I thought the best way to do this would be a count(if(( statement, but this
doesn't seem to be working. Any ideas??

"T. Valko" wrote:

Try it like this:

=COUNTIF(A1:A100,"<="&B1)

{=Count(if((A1:A100,"<=B1")*(C1:C100,"=B1),(D1:D 100))}

Not sure what you want to do with that.


--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
I am trying to write an Countif statement that I will eventually turn into
an
array. However, I am having problems with the dates. My statement now
is:

Countif(A1:A100,"<=B1")

In this statement A1:A100 are all dates, and so is B1. The function just
gives me zero. However if I change the statement to:

Countif(A1:A100,"<=1/1/2009")

Then the statement works. If I physically type in the date it works fine,
but when I try to have it look up the date from another cell it doesn't
work.
Any ideas?

Ulimately I want the equation to be

{=Count(if((A1:A100,"<=B1")*(C1:C100,"=B1),(D1:D1 00))}

I need to get past the first error before I can proceed.

Provide more detail on how your data is setup.

--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
I have a lot of data for some items of work. The items can have either a
status of open, repaired, closed, or rejected. I need to determine the
number of each on any given day.

So if an item was opened on 12/1/08, repaired on 12/10/08 and closed on
12/20/08, then I want to be able to right an equation that will say on
12/15/08 we had 1 item that was repaired. As of right now I have over
1,500
activities. I just need to know how many open, repaired, closed, or
rejected
items on any day.

I thought the best way to do this would be a count(if(( statement, but
this
doesn't seem to be working. Any ideas??

"T. Valko" wrote:

Try it like this:

=COUNTIF(A1:A100,"<="&B1)

{=Count(if((A1:A100,"<=B1")*(C1:C100,"=B1),(D1:D 100))}

Not sure what you want to do with that.


--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
I am trying to write an Countif statement that I will eventually turn
into
an
array. However, I am having problems with the dates. My statement now
is:

Countif(A1:A100,"<=B1")

In this statement A1:A100 are all dates, and so is B1. The function
just
gives me zero. However if I change the statement to:

Countif(A1:A100,"<=1/1/2009")

Then the statement works. If I physically type in the date it works
fine,
but when I try to have it look up the date from another cell it doesn't
work.
Any ideas?

Ulimately I want the equation to be

{=Count(if((A1:A100,"<=B1")*(C1:C100,"=B1),(D1:D1 00))}

I need to get past the first error before I can proceed.

Column B = ID Numbers (3 or 4 digit numbers)
Column G = Date Opened (MM/DD/YYYY)
Column L = Date Repaired (Empty if Status = Open)
Column M = Date Inspected (Empty if Status = Repaired or Open)
Column N = Status (Open, Repaired, Closed, Rejected)

Currently I have over 1500 items, but this could double. Is this what you
are looking for?


"T. Valko" wrote:

Provide more detail on how your data is setup.

--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
I have a lot of data for some items of work. The items can have either a
status of open, repaired, closed, or rejected. I need to determine the
number of each on any given day.

So if an item was opened on 12/1/08, repaired on 12/10/08 and closed on
12/20/08, then I want to be able to right an equation that will say on
12/15/08 we had 1 item that was repaired. As of right now I have over
1,500
activities. I just need to know how many open, repaired, closed, or
rejected
items on any day.

I thought the best way to do this would be a count(if(( statement, but
this
doesn't seem to be working. Any ideas??

"T. Valko" wrote:

Try it like this:

=COUNTIF(A1:A100,"<="&B1)

{=Count(if((A1:A100,"<=B1")*(C1:C100,"=B1),(D1:D 100))}

Not sure what you want to do with that.


--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
I am trying to write an Countif statement that I will eventually turn
into
an
array. However, I am having problems with the dates. My statement now
is:

Countif(A1:A100,"<=B1")

In this statement A1:A100 are all dates, and so is B1. The function
just
gives me zero. However if I change the statement to:

Countif(A1:A100,"<=1/1/2009")

Then the statement works. If I physically type in the date it works
fine,
but when I try to have it look up the date from another cell it doesn't
work.
Any ideas?

Ulimately I want the equation to be

{=Count(if((A1:A100,"<=B1")*(C1:C100,"=B1),(D1:D1 00))}

I need to get past the first error before I can proceed.

Is this what you are looking for?

Yeah, that helps but I'm still not understanding what you want to do.

You said:

So if an item was opened on 12/1/08, repaired on 12/10/08
and closed on 12/20/08, then I want to be able to right an
equation that will say on 12/15/08 we had 1 item that
was repaired.

And said your layout is:

Column B = ID Numbers (3 or 4 digit numbers)
Column G = Date Opened (MM/DD/YYYY)
Column L = Date Repaired (Empty if Status = Open)
Column M = Date Inspected (Empty if Status = Repaired or Open)
Column N = Status (Open, Repaired, Closed, Rejected)

But I don't see anywhere in your layout a place for a date closed. Also, if
the item was repaired on 12/10/2008 how does the date 12/15/2008 correlate
to the item having been repaired on 12/10/2008?


--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
Column B = ID Numbers (3 or 4 digit numbers)
Column G = Date Opened (MM/DD/YYYY)
Column L = Date Repaired (Empty if Status = Open)
Column M = Date Inspected (Empty if Status = Repaired or Open)
Column N = Status (Open, Repaired, Closed, Rejected)

Currently I have over 1500 items, but this could double. Is this what you
are looking for?


"T. Valko" wrote:

Provide more detail on how your data is setup.

--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
I have a lot of data for some items of work. The items can have either
a
status of open, repaired, closed, or rejected. I need to determine the
number of each on any given day.

So if an item was opened on 12/1/08, repaired on 12/10/08 and closed on
12/20/08, then I want to be able to right an equation that will say on
12/15/08 we had 1 item that was repaired. As of right now I have over
1,500
activities. I just need to know how many open, repaired, closed, or
rejected
items on any day.

I thought the best way to do this would be a count(if(( statement, but
this
doesn't seem to be working. Any ideas??

"T. Valko" wrote:

Try it like this:

=COUNTIF(A1:A100,"<="&B1)

{=Count(if((A1:A100,"<=B1")*(C1:C100,"=B1),(D1:D 100))}

Not sure what you want to do with that.


--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
I am trying to write an Countif statement that I will eventually turn
into
an
array. However, I am having problems with the dates. My statement
now
is:

Countif(A1:A100,"<=B1")

In this statement A1:A100 are all dates, and so is B1. The function
just
gives me zero. However if I change the statement to:

Countif(A1:A100,"<=1/1/2009")

Then the statement works. If I physically type in the date it works
fine,
but when I try to have it look up the date from another cell it
doesn't
work.
Any ideas?

Ulimately I want the equation to be

{=Count(if((A1:A100,"<=B1")*(C1:C100,"=B1),(D1:D1 00))}

I need to get past the first error before I can proceed.

Here is an example of the data:
B G L M N
1 1/1 1/5 1/10 Closed
2 1/2 1/3 1/4 Closed
3 1/1 Open
4 1/5 1/15 Repaired
5 1/15 1/20 1/21 Rejected

Here is what I am looking for

On Day X how many did I have open? How Many were closed? How many were
repaired? How many were rejected. So for example on 1/3 I would have had 2
open (#1&#3) & 1 Closed #2.

Or on 1/10 I would have had 2 open (#3 & #4) & two closed (#1&#2). Even
though the status has changed on number 4 to repaired, it wasn't repaired
until 1/15 so on 1/10 it would have been open.

Does that help?



"T. Valko" wrote:

Is this what you are looking for?

Yeah, that helps but I'm still not understanding what you want to do.

You said:

So if an item was opened on 12/1/08, repaired on 12/10/08
and closed on 12/20/08, then I want to be able to right an
equation that will say on 12/15/08 we had 1 item that
was repaired.

And said your layout is:

Column B = ID Numbers (3 or 4 digit numbers)
Column G = Date Opened (MM/DD/YYYY)
Column L = Date Repaired (Empty if Status = Open)
Column M = Date Inspected (Empty if Status = Repaired or Open)
Column N = Status (Open, Repaired, Closed, Rejected)

But I don't see anywhere in your layout a place for a date closed. Also, if
the item was repaired on 12/10/2008 how does the date 12/15/2008 correlate
to the item having been repaired on 12/10/2008?


--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
Column B = ID Numbers (3 or 4 digit numbers)
Column G = Date Opened (MM/DD/YYYY)
Column L = Date Repaired (Empty if Status = Open)
Column M = Date Inspected (Empty if Status = Repaired or Open)
Column N = Status (Open, Repaired, Closed, Rejected)

Currently I have over 1500 items, but this could double. Is this what you
are looking for?


"T. Valko" wrote:

Provide more detail on how your data is setup.

--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
I have a lot of data for some items of work. The items can have either
a
status of open, repaired, closed, or rejected. I need to determine the
number of each on any given day.

So if an item was opened on 12/1/08, repaired on 12/10/08 and closed on
12/20/08, then I want to be able to right an equation that will say on
12/15/08 we had 1 item that was repaired. As of right now I have over
1,500
activities. I just need to know how many open, repaired, closed, or
rejected
items on any day.

I thought the best way to do this would be a count(if(( statement, but
this
doesn't seem to be working. Any ideas??

"T. Valko" wrote:

Try it like this:

=COUNTIF(A1:A100,"<="&B1)

{=Count(if((A1:A100,"<=B1")*(C1:C100,"=B1),(D1:D 100))}

Not sure what you want to do with that.


--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
I am trying to write an Countif statement that I will eventually turn
into
an
array. However, I am having problems with the dates. My statement
now
is:

Countif(A1:A100,"<=B1")

In this statement A1:A100 are all dates, and so is B1. The function
just
gives me zero. However if I change the statement to:

Countif(A1:A100,"<=1/1/2009")

Then the statement works. If I physically type in the date it works
fine,
but when I try to have it look up the date from another cell it
doesn't
work.
Any ideas?

Ulimately I want the equation to be

{=Count(if((A1:A100,"<=B1")*(C1:C100,"=B1),(D1:D1 00))}

I need to get past the first error before I can proceed.

Ok, these formulas return the results you're looking for based on your
sample data:

B15 = input date = 1/3/2009

Open:

=SUMPRODUCT(--(G2:G6<=B15),(L2:L6="")+(L2:L6B15))

Closed:

=SUMPRODUCT(--(G2:G6<=B15),--(L2:L6<""),--(L2:L6<=B15))


--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
Here is an example of the data:
B G L M N
1 1/1 1/5 1/10 Closed
2 1/2 1/3 1/4 Closed
3 1/1 Open
4 1/5 1/15 Repaired
5 1/15 1/20 1/21 Rejected

Here is what I am looking for

On Day X how many did I have open? How Many were closed? How many were
repaired? How many were rejected. So for example on 1/3 I would have had
2
open (#1&#3) & 1 Closed #2.

Or on 1/10 I would have had 2 open (#3 & #4) & two closed (#1&#2). Even
though the status has changed on number 4 to repaired, it wasn't repaired
until 1/15 so on 1/10 it would have been open.

Does that help?



"T. Valko" wrote:

Is this what you are looking for?

Yeah, that helps but I'm still not understanding what you want to do.

You said:

So if an item was opened on 12/1/08, repaired on 12/10/08
and closed on 12/20/08, then I want to be able to right an
equation that will say on 12/15/08 we had 1 item that
was repaired.

And said your layout is:

Column B = ID Numbers (3 or 4 digit numbers)
Column G = Date Opened (MM/DD/YYYY)
Column L = Date Repaired (Empty if Status = Open)
Column M = Date Inspected (Empty if Status = Repaired or Open)
Column N = Status (Open, Repaired, Closed, Rejected)

But I don't see anywhere in your layout a place for a date closed. Also,
if
the item was repaired on 12/10/2008 how does the date 12/15/2008
correlate
to the item having been repaired on 12/10/2008?


--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
Column B = ID Numbers (3 or 4 digit numbers)
Column G = Date Opened (MM/DD/YYYY)
Column L = Date Repaired (Empty if Status = Open)
Column M = Date Inspected (Empty if Status = Repaired or Open)
Column N = Status (Open, Repaired, Closed, Rejected)

Currently I have over 1500 items, but this could double. Is this what
you
are looking for?


"T. Valko" wrote:

Provide more detail on how your data is setup.

--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
I have a lot of data for some items of work. The items can have
either
a
status of open, repaired, closed, or rejected. I need to determine
the
number of each on any given day.

So if an item was opened on 12/1/08, repaired on 12/10/08 and closed
on
12/20/08, then I want to be able to right an equation that will say
on
12/15/08 we had 1 item that was repaired. As of right now I have
over
1,500
activities. I just need to know how many open, repaired, closed, or
rejected
items on any day.

I thought the best way to do this would be a count(if(( statement,
but
this
doesn't seem to be working. Any ideas??

"T. Valko" wrote:

Try it like this:

=COUNTIF(A1:A100,"<="&B1)

{=Count(if((A1:A100,"<=B1")*(C1:C100,"=B1),(D1:D 100))}

Not sure what you want to do with that.


--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
I am trying to write an Countif statement that I will eventually
turn
into
an
array. However, I am having problems with the dates. My
statement
now
is:

Countif(A1:A100,"<=B1")

In this statement A1:A100 are all dates, and so is B1. The
function
just
gives me zero. However if I change the statement to:

Countif(A1:A100,"<=1/1/2009")

Then the statement works. If I physically type in the date it
works
fine,
but when I try to have it look up the date from another cell it
doesn't
work.
Any ideas?

Ulimately I want the equation to be

{=Count(if((A1:A100,"<=B1")*(C1:C100,"=B1),(D1:D1 00))}

I need to get past the first error before I can proceed.

What do the -- do in the sumproduct(--( ? I am not familar with this
function. Thanks.

"T. Valko" wrote:

Ok, these formulas return the results you're looking for based on your
sample data:

B15 = input date = 1/3/2009

Open:

=SUMPRODUCT(--(G2:G6<=B15),(L2:L6="")+(L2:L6B15))

Closed:

=SUMPRODUCT(--(G2:G6<=B15),--(L2:L6<""),--(L2:L6<=B15))


--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
Here is an example of the data:
B G L M N
1 1/1 1/5 1/10 Closed
2 1/2 1/3 1/4 Closed
3 1/1 Open
4 1/5 1/15 Repaired
5 1/15 1/20 1/21 Rejected

Here is what I am looking for

On Day X how many did I have open? How Many were closed? How many were
repaired? How many were rejected. So for example on 1/3 I would have had
2
open (#1&#3) & 1 Closed #2.

Or on 1/10 I would have had 2 open (#3 & #4) & two closed (#1&#2). Even
though the status has changed on number 4 to repaired, it wasn't repaired
until 1/15 so on 1/10 it would have been open.

Does that help?



"T. Valko" wrote:

Is this what you are looking for?

Yeah, that helps but I'm still not understanding what you want to do.

You said:

So if an item was opened on 12/1/08, repaired on 12/10/08
and closed on 12/20/08, then I want to be able to right an
equation that will say on 12/15/08 we had 1 item that
was repaired.

And said your layout is:

Column B = ID Numbers (3 or 4 digit numbers)
Column G = Date Opened (MM/DD/YYYY)
Column L = Date Repaired (Empty if Status = Open)
Column M = Date Inspected (Empty if Status = Repaired or Open)
Column N = Status (Open, Repaired, Closed, Rejected)

But I don't see anywhere in your layout a place for a date closed. Also,
if
the item was repaired on 12/10/2008 how does the date 12/15/2008
correlate
to the item having been repaired on 12/10/2008?


--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
Column B = ID Numbers (3 or 4 digit numbers)
Column G = Date Opened (MM/DD/YYYY)
Column L = Date Repaired (Empty if Status = Open)
Column M = Date Inspected (Empty if Status = Repaired or Open)
Column N = Status (Open, Repaired, Closed, Rejected)

Currently I have over 1500 items, but this could double. Is this what
you
are looking for?


"T. Valko" wrote:

Provide more detail on how your data is setup.

--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
I have a lot of data for some items of work. The items can have
either
a
status of open, repaired, closed, or rejected. I need to determine
the
number of each on any given day.

So if an item was opened on 12/1/08, repaired on 12/10/08 and closed
on
12/20/08, then I want to be able to right an equation that will say
on
12/15/08 we had 1 item that was repaired. As of right now I have
over
1,500
activities. I just need to know how many open, repaired, closed, or
rejected
items on any day.

I thought the best way to do this would be a count(if(( statement,
but
this
doesn't seem to be working. Any ideas??

"T. Valko" wrote:

Try it like this:

=COUNTIF(A1:A100,"<="&B1)

{=Count(if((A1:A100,"<=B1")*(C1:C100,"=B1),(D1:D 100))}

Not sure what you want to do with that.


--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
I am trying to write an Countif statement that I will eventually
turn
into
an
array. However, I am having problems with the dates. My
statement
now
is:

Countif(A1:A100,"<=B1")

In this statement A1:A100 are all dates, and so is B1. The
function
just
gives me zero. However if I change the statement to:

Countif(A1:A100,"<=1/1/2009")

Then the statement works. If I physically type in the date it
works
fine,
but when I try to have it look up the date from another cell it
doesn't
work.
Any ideas?

Ulimately I want the equation to be

{=Count(if((A1:A100,"<=B1")*(C1:C100,"=B1),(D1:D1 00))}

I need to get past the first error before I can proceed.

HI
The -- stuff are called double Unary they changes trues and falses
to 1's and 0's. Logic expression to numeric expression.
HTH
John
"Thebaran" wrote in message
...
What do the -- do in the sumproduct(--( ? I am not familar with this
function. Thanks.

"T. Valko" wrote:

Ok, these formulas return the results you're looking for based on your
sample data:

B15 = input date = 1/3/2009

Open:

=SUMPRODUCT(--(G2:G6<=B15),(L2:L6="")+(L2:L6B15))

Closed:

=SUMPRODUCT(--(G2:G6<=B15),--(L2:L6<""),--(L2:L6<=B15))


--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
Here is an example of the data:
B G L M N
1 1/1 1/5 1/10 Closed
2 1/2 1/3 1/4 Closed
3 1/1 Open
4 1/5 1/15 Repaired
5 1/15 1/20 1/21 Rejected

Here is what I am looking for

On Day X how many did I have open? How Many were closed? How many
were
repaired? How many were rejected. So for example on 1/3 I would have
had
2
open (#1&#3) & 1 Closed #2.

Or on 1/10 I would have had 2 open (#3 & #4) & two closed (#1&#2).
Even
though the status has changed on number 4 to repaired, it wasn't
repaired
until 1/15 so on 1/10 it would have been open.

Does that help?



"T. Valko" wrote:

Is this what you are looking for?

Yeah, that helps but I'm still not understanding what you want to do.

You said:

So if an item was opened on 12/1/08, repaired on 12/10/08
and closed on 12/20/08, then I want to be able to right an
equation that will say on 12/15/08 we had 1 item that
was repaired.

And said your layout is:

Column B = ID Numbers (3 or 4 digit numbers)
Column G = Date Opened (MM/DD/YYYY)
Column L = Date Repaired (Empty if Status = Open)
Column M = Date Inspected (Empty if Status = Repaired or Open)
Column N = Status (Open, Repaired, Closed, Rejected)

But I don't see anywhere in your layout a place for a date closed.
Also,
if
the item was repaired on 12/10/2008 how does the date 12/15/2008
correlate
to the item having been repaired on 12/10/2008?


--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
Column B = ID Numbers (3 or 4 digit numbers)
Column G = Date Opened (MM/DD/YYYY)
Column L = Date Repaired (Empty if Status = Open)
Column M = Date Inspected (Empty if Status = Repaired or Open)
Column N = Status (Open, Repaired, Closed, Rejected)

Currently I have over 1500 items, but this could double. Is this
what
you
are looking for?


"T. Valko" wrote:

Provide more detail on how your data is setup.

--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
I have a lot of data for some items of work. The items can have
either
a
status of open, repaired, closed, or rejected. I need to
determine
the
number of each on any given day.

So if an item was opened on 12/1/08, repaired on 12/10/08 and
closed
on
12/20/08, then I want to be able to right an equation that will
say
on
12/15/08 we had 1 item that was repaired. As of right now I have
over
1,500
activities. I just need to know how many open, repaired, closed,
or
rejected
items on any day.

I thought the best way to do this would be a count(if((
statement,
but
this
doesn't seem to be working. Any ideas??

"T. Valko" wrote:

Try it like this:

=COUNTIF(A1:A100,"<="&B1)

{=Count(if((A1:A100,"<=B1")*(C1:C100,"=B1),(D1:D 100))}

Not sure what you want to do with that.


--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
I am trying to write an Countif statement that I will
eventually
turn
into
an
array. However, I am having problems with the dates. My
statement
now
is:

Countif(A1:A100,"<=B1")

In this statement A1:A100 are all dates, and so is B1. The
function
just
gives me zero. However if I change the statement to:

Countif(A1:A100,"<=1/1/2009")

Then the statement works. If I physically type in the date it
works
fine,
but when I try to have it look up the date from another cell
it
doesn't
work.
Any ideas?

Ulimately I want the equation to be

{=Count(if((A1:A100,"<=B1")*(C1:C100,"=B1),(D1:D1 00))}

I need to get past the first error before I can proceed.

See this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
What do the -- do in the sumproduct(--( ? I am not familar with this
function. Thanks.

"T. Valko" wrote:

Ok, these formulas return the results you're looking for based on your
sample data:

B15 = input date = 1/3/2009

Open:

=SUMPRODUCT(--(G2:G6<=B15),(L2:L6="")+(L2:L6B15))

Closed:

=SUMPRODUCT(--(G2:G6<=B15),--(L2:L6<""),--(L2:L6<=B15))


--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
Here is an example of the data:
B G L M N
1 1/1 1/5 1/10 Closed
2 1/2 1/3 1/4 Closed
3 1/1 Open
4 1/5 1/15 Repaired
5 1/15 1/20 1/21 Rejected

Here is what I am looking for

On Day X how many did I have open? How Many were closed? How many
were
repaired? How many were rejected. So for example on 1/3 I would have
had
2
open (#1&#3) & 1 Closed #2.

Or on 1/10 I would have had 2 open (#3 & #4) & two closed (#1&#2).
Even
though the status has changed on number 4 to repaired, it wasn't
repaired
until 1/15 so on 1/10 it would have been open.

Does that help?



"T. Valko" wrote:

Is this what you are looking for?

Yeah, that helps but I'm still not understanding what you want to do.

You said:

So if an item was opened on 12/1/08, repaired on 12/10/08
and closed on 12/20/08, then I want to be able to right an
equation that will say on 12/15/08 we had 1 item that
was repaired.

And said your layout is:

Column B = ID Numbers (3 or 4 digit numbers)
Column G = Date Opened (MM/DD/YYYY)
Column L = Date Repaired (Empty if Status = Open)
Column M = Date Inspected (Empty if Status = Repaired or Open)
Column N = Status (Open, Repaired, Closed, Rejected)

But I don't see anywhere in your layout a place for a date closed.
Also,
if
the item was repaired on 12/10/2008 how does the date 12/15/2008
correlate
to the item having been repaired on 12/10/2008?


--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
Column B = ID Numbers (3 or 4 digit numbers)
Column G = Date Opened (MM/DD/YYYY)
Column L = Date Repaired (Empty if Status = Open)
Column M = Date Inspected (Empty if Status = Repaired or Open)
Column N = Status (Open, Repaired, Closed, Rejected)

Currently I have over 1500 items, but this could double. Is this
what
you
are looking for?


"T. Valko" wrote:

Provide more detail on how your data is setup.

--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
I have a lot of data for some items of work. The items can have
either
a
status of open, repaired, closed, or rejected. I need to
determine
the
number of each on any given day.

So if an item was opened on 12/1/08, repaired on 12/10/08 and
closed
on
12/20/08, then I want to be able to right an equation that will
say
on
12/15/08 we had 1 item that was repaired. As of right now I have
over
1,500
activities. I just need to know how many open, repaired, closed,
or
rejected
items on any day.

I thought the best way to do this would be a count(if((
statement,
but
this
doesn't seem to be working. Any ideas??

"T. Valko" wrote:

Try it like this:

=COUNTIF(A1:A100,"<="&B1)

{=Count(if((A1:A100,"<=B1")*(C1:C100,"=B1),(D1:D 100))}

Not sure what you want to do with that.


--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
I am trying to write an Countif statement that I will
eventually
turn
into
an
array. However, I am having problems with the dates. My
statement
now
is:

Countif(A1:A100,"<=B1")

In this statement A1:A100 are all dates, and so is B1. The
function
just
gives me zero. However if I change the statement to:

Countif(A1:A100,"<=1/1/2009")

Then the statement works. If I physically type in the date it
works
fine,
but when I try to have it look up the date from another cell
it
doesn't
work.
Any ideas?

Ulimately I want the equation to be

{=Count(if((A1:A100,"<=B1")*(C1:C100,"=B1),(D1:D1 00))}

I need to get past the first error before I can proceed.

Your equation for open items worked great. The only problem I found was that
I don't want to continually update the equation as data is added. So if I
know I am going to have 20 rows of data I would right the equation as

=SUMPRODUCT(--(G2:G20<=B15),(L2:L20="")+(L2:L20B15))

Which right now doesn't work. What is the best way to handle these blank
rows. Would it be something like:

=SUMPRODUCT(--(G2:G20<=B15)-(G2:G20=""),(L2:L20="")+(L2:L20B15))

Also the equation for the closed values did not return the correct data. It
appears it is returning the value for all of the repaired, closed, and
rejected items, and not just the closed ones.

Thanks for the help.

"T. Valko" wrote:

See this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
What do the -- do in the sumproduct(--( ? I am not familar with this
function. Thanks.

"T. Valko" wrote:

Ok, these formulas return the results you're looking for based on your
sample data:

B15 = input date = 1/3/2009

Open:

=SUMPRODUCT(--(G2:G6<=B15),(L2:L6="")+(L2:L6B15))

Closed:

=SUMPRODUCT(--(G2:G6<=B15),--(L2:L6<""),--(L2:L6<=B15))


--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
Here is an example of the data:
B G L M N
1 1/1 1/5 1/10 Closed
2 1/2 1/3 1/4 Closed
3 1/1 Open
4 1/5 1/15 Repaired
5 1/15 1/20 1/21 Rejected

Here is what I am looking for

On Day X how many did I have open? How Many were closed? How many
were
repaired? How many were rejected. So for example on 1/3 I would have
had
2
open (#1&#3) & 1 Closed #2.

Or on 1/10 I would have had 2 open (#3 & #4) & two closed (#1&#2).
Even
though the status has changed on number 4 to repaired, it wasn't
repaired
until 1/15 so on 1/10 it would have been open.

Does that help?



"T. Valko" wrote:

Is this what you are looking for?

Yeah, that helps but I'm still not understanding what you want to do.

You said:

So if an item was opened on 12/1/08, repaired on 12/10/08
and closed on 12/20/08, then I want to be able to right an
equation that will say on 12/15/08 we had 1 item that
was repaired.

And said your layout is:

Column B = ID Numbers (3 or 4 digit numbers)
Column G = Date Opened (MM/DD/YYYY)
Column L = Date Repaired (Empty if Status = Open)
Column M = Date Inspected (Empty if Status = Repaired or Open)
Column N = Status (Open, Repaired, Closed, Rejected)

But I don't see anywhere in your layout a place for a date closed.
Also,
if
the item was repaired on 12/10/2008 how does the date 12/15/2008
correlate
to the item having been repaired on 12/10/2008?


--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
Column B = ID Numbers (3 or 4 digit numbers)
Column G = Date Opened (MM/DD/YYYY)
Column L = Date Repaired (Empty if Status = Open)
Column M = Date Inspected (Empty if Status = Repaired or Open)
Column N = Status (Open, Repaired, Closed, Rejected)

Currently I have over 1500 items, but this could double. Is this
what
you
are looking for?


"T. Valko" wrote:

Provide more detail on how your data is setup.

--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
I have a lot of data for some items of work. The items can have
either
a
status of open, repaired, closed, or rejected. I need to
determine
the
number of each on any given day.

So if an item was opened on 12/1/08, repaired on 12/10/08 and
closed
on
12/20/08, then I want to be able to right an equation that will
say
on
12/15/08 we had 1 item that was repaired. As of right now I have
over
1,500
activities. I just need to know how many open, repaired, closed,
or
rejected
items on any day.

I thought the best way to do this would be a count(if((
statement,
but
this
doesn't seem to be working. Any ideas??

"T. Valko" wrote:

Try it like this:

=COUNTIF(A1:A100,"<="&B1)

{=Count(if((A1:A100,"<=B1")*(C1:C100,"=B1),(D1:D 100))}

Not sure what you want to do with that.


--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
I am trying to write an Countif statement that I will
eventually
turn
into
an
array. However, I am having problems with the dates. My
statement
now
is:

Countif(A1:A100,"<=B1")

In this statement A1:A100 are all dates, and so is B1. The
function
just
gives me zero. However if I change the statement to:

Countif(A1:A100,"<=1/1/2009")

Then the statement works. If I physically type in the date it
works
fine,
but when I try to have it look up the date from another cell
it
doesn't
work.
Any ideas?

Ulimately I want the equation to be

{=Count(if((A1:A100,"<=B1")*(C1:C100,"=B1),(D1:D1 00))}

I need to get past the first error before I can proceed.

Add a test that there is in fact a date entered in column G:

=SUMPRODUCT(--(ISNUMBER(G2:G20)),--(G2:G20<=B15),(L2:L20="")+(L2:L20B15))

--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
Your equation for open items worked great. The only problem I found was
that
I don't want to continually update the equation as data is added. So if I
know I am going to have 20 rows of data I would right the equation as

=SUMPRODUCT(--(G2:G20<=B15),(L2:L20="")+(L2:L20B15))

Which right now doesn't work. What is the best way to handle these blank
rows. Would it be something like:

=SUMPRODUCT(--(G2:G20<=B15)-(G2:G20=""),(L2:L20="")+(L2:L20B15))

Also the equation for the closed values did not return the correct data.
It
appears it is returning the value for all of the repaired, closed, and
rejected items, and not just the closed ones.

Thanks for the help.

"T. Valko" wrote:

See this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
What do the -- do in the sumproduct(--( ? I am not familar with this
function. Thanks.

"T. Valko" wrote:

Ok, these formulas return the results you're looking for based on your
sample data:

B15 = input date = 1/3/2009

Open:

=SUMPRODUCT(--(G2:G6<=B15),(L2:L6="")+(L2:L6B15))

Closed:

=SUMPRODUCT(--(G2:G6<=B15),--(L2:L6<""),--(L2:L6<=B15))


--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
Here is an example of the data:
B G L M N
1 1/1 1/5 1/10 Closed
2 1/2 1/3 1/4 Closed
3 1/1 Open
4 1/5 1/15 Repaired
5 1/15 1/20 1/21 Rejected

Here is what I am looking for

On Day X how many did I have open? How Many were closed? How many
were
repaired? How many were rejected. So for example on 1/3 I would
have
had
2
open (#1&#3) & 1 Closed #2.

Or on 1/10 I would have had 2 open (#3 & #4) & two closed (#1&#2).
Even
though the status has changed on number 4 to repaired, it wasn't
repaired
until 1/15 so on 1/10 it would have been open.

Does that help?



"T. Valko" wrote:

Is this what you are looking for?

Yeah, that helps but I'm still not understanding what you want to
do.

You said:

So if an item was opened on 12/1/08, repaired on 12/10/08
and closed on 12/20/08, then I want to be able to right an
equation that will say on 12/15/08 we had 1 item that
was repaired.

And said your layout is:

Column B = ID Numbers (3 or 4 digit numbers)
Column G = Date Opened (MM/DD/YYYY)
Column L = Date Repaired (Empty if Status = Open)
Column M = Date Inspected (Empty if Status = Repaired or Open)
Column N = Status (Open, Repaired, Closed, Rejected)

But I don't see anywhere in your layout a place for a date closed.
Also,
if
the item was repaired on 12/10/2008 how does the date 12/15/2008
correlate
to the item having been repaired on 12/10/2008?


--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
Column B = ID Numbers (3 or 4 digit numbers)
Column G = Date Opened (MM/DD/YYYY)
Column L = Date Repaired (Empty if Status = Open)
Column M = Date Inspected (Empty if Status = Repaired or Open)
Column N = Status (Open, Repaired, Closed, Rejected)

Currently I have over 1500 items, but this could double. Is this
what
you
are looking for?


"T. Valko" wrote:

Provide more detail on how your data is setup.

--
Biff
Microsoft Excel MVP


"Thebaran" wrote in message
...
I have a lot of data for some items of work. The items can
have
either
a
status of open, repaired, closed, or rejected. I need to
determine
the
number of each on any given day.

So if an item was opened on 12/1/08, repaired on 12/10/08 and
closed
on
12/20/08, then I want to be able to right an equation that
will
say
on
12/15/08 we had 1 item that was repaired. As of right now I
have
over
1,500
activities. I just need to know how many open, repaired,
closed,
or
rejected
items on any day.

I thought the best way to do this would be a count(if((
statement,
but
this
doesn't seem to be working. Any ideas??

"T. Valko" wrote:

Try it like this:

=COUNTIF(A1:A100,"<="&B1)

{=Count(if((A1:A100,"<=B1")*(C1:C100,"=B1),(D1:D 100))}

Not sure what you want to do with that.


--
Biff
Microsoft Excel MVP


"Thebaran" wrote in
message
...
I am trying to write an Countif statement that I will
eventually
turn
into
an
array. However, I am having problems with the dates. My
statement
now
is:

Countif(A1:A100,"<=B1")

In this statement A1:A100 are all dates, and so is B1. The
function
just
gives me zero. However if I change the statement to:

Countif(A1:A100,"<=1/1/2009")

Then the statement works. If I physically type in the date
it
works
fine,
but when I try to have it look up the date from another
cell
it
doesn't
work.
Any ideas?

Ulimately I want the equation to be

{=Count(if((A1:A100,"<=B1")*(C1:C100,"=B1),(D1:D1 00))}

I need to get past the first error before I can proceed.