Countif statement using dates

[Thebaran]

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),(D1100))}

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

 

[Elkar]

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),D1100)

HTH
Elkar

 

[T. Valko]

Try it like this:

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

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

Not sure what you want to do with that.

 

[Mike H]

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

 

[Chip Pearson]

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)

 

[Thebaran]

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]

Provide more detail on how your data is setup.

 

[Thebaran]

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]

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?

 

[Thebaran]

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]

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:L6>B15))

Closed:

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

 

[Thebaran]

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

 

[John]

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

 

[T. Valko]

See this:

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

 

[Thebaran]

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:L20>B15))

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:L20>B15))

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]

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

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