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) & 1 Closed #2.
Or on 1/10 I would have had 2 open (#3 & #4) & two closed (#1).
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.