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.

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.

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.

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.