Hello,

I need help figuring out a formula that counts the number of incomplete jobs
for a specific worker that are within the next seven days. I would only want
to count the "Date Completed" column if it was blank and within seven days
from today's date. I've tried using sumproduct, but I can't seem to figure
out how to get the date calculations to work properly.

My data looks similar to this

Worker Due Date Date Completed
Bob 1/18/2010 1/13/2010
Bob 1/21/2010
Bob 2/20/2010
Tom 1/20/2010
Tom 2/10/2010 1/10/2010

You help is much appreciated.

=SUMPRODUCT(--(B2:B22<"")*--(B2:B22TODAY()+7),--(C2:C22<""))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Deckbeers" wrote in message
...
Hello,

I need help figuring out a formula that counts the number of incomplete
jobs
for a specific worker that are within the next seven days. I would only
want
to count the "Date Completed" column if it was blank and within seven days
from today's date. I've tried using sumproduct, but I can't seem to figure
out how to get the date calculations to work properly.

My data looks similar to this

Worker Due Date Date Completed
Bob 1/18/2010 1/13/2010
Bob 1/21/2010
Bob 2/20/2010
Tom 1/20/2010
Tom 2/10/2010 1/10/2010

You help is much appreciated.

That's great! It gets me the number for the first worker.

I'm trying to build a summary table, so how would I create those numbers for
each worker? I could be an ID10T and have missed your point. Do I need column
A in there somewhere?

"Don Guillett" wrote:

=SUMPRODUCT(--(B2:B22<"")*--(B2:B22TODAY()+7),--(C2:C22<""))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Deckbeers" wrote in message
...
Hello,

I need help figuring out a formula that counts the number of incomplete
jobs
for a specific worker that are within the next seven days. I would only
want
to count the "Date Completed" column if it was blank and within seven days
from today's date. I've tried using sumproduct, but I can't seem to figure
out how to get the date calculations to work properly.

My data looks similar to this

Worker Due Date Date Completed
Bob 1/18/2010 1/13/2010
Bob 1/21/2010
Bob 2/20/2010
Tom 1/20/2010
Tom 2/10/2010 1/10/2010

You help is much appreciated.

.

Try these:

=SUM(IF(A5:A9="Bob",IF(C5:C9="",1,0),0))

Enter with CTRL + SHIFT + ENTER, you will get { } around the formula. Note
that Bob can be a cell reference (F1 here) from a drop down list.

=SUM(IF(A5:A9=F1,IF(C5:C9="",1,0),0))

Also entered with with CTRL + SHIFT + ENTER.

or with a simple ENTER

=SUMPRODUCT((A5:A9="Bob")*(C5:C9=""))

Which can also be a cell reference for the name and use ENTER.

=SUMPRODUCT((A5:A9=F1)*(C5:C9=""))

HTH
Regards,
Howard

"Deckbeers" wrote in message
...
Hello,

I need help figuring out a formula that counts the number of incomplete
jobs
for a specific worker that are within the next seven days. I would only
want
to count the "Date Completed" column if it was blank and within seven days
from today's date. I've tried using sumproduct, but I can't seem to figure
out how to get the date calculations to work properly.

My data looks similar to this

Worker Due Date Date Completed
Bob 1/18/2010 1/13/2010
Bob 1/21/2010
Bob 2/20/2010
Tom 1/20/2010
Tom 2/10/2010 1/10/2010

You help is much appreciated.

I assume that the asterisk was intended to be a comma, Don? You wouldn't
need the first two double unary minuses if you were doing the multiplication
with an asterisk, of course.
--
David Biddulph


Don Guillett wrote:
=SUMPRODUCT(--(B2:B22<"")*--(B2:B22TODAY()+7),--(C2:C22<""))

Hello,

I need help figuring out a formula that counts the number of
incomplete jobs
for a specific worker that are within the next seven days. I would
only want
to count the "Date Completed" column if it was blank and within
seven days from today's date. I've tried using sumproduct, but I
can't seem to figure out how to get the date calculations to work
properly. My data looks similar to this

Worker Due Date Date Completed
Bob 1/18/2010 1/13/2010
Bob 1/21/2010
Bob 2/20/2010
Tom 1/20/2010
Tom 2/10/2010 1/10/2010

You help is much appreciated.

Testing shows it works both ways...

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
I assume that the asterisk was intended to be a comma, Don? You wouldn't
need the first two double unary minuses if you were doing the
multiplication with an asterisk, of course.
--
David Biddulph


Don Guillett wrote:
=SUMPRODUCT(--(B2:B22<"")*--(B2:B22TODAY()+7),--(C2:C22<""))

Hello,

I need help figuring out a formula that counts the number of
incomplete jobs
for a specific worker that are within the next seven days. I would
only want
to count the "Date Completed" column if it was blank and within
seven days from today's date. I've tried using sumproduct, but I
can't seem to figure out how to get the date calculations to work
properly. My data looks similar to this

Worker Due Date Date Completed
Bob 1/18/2010 1/13/2010
Bob 1/21/2010
Bob 2/20/2010
Tom 1/20/2010
Tom 2/10/2010 1/10/2010

You help is much appreciated.

If you are referring to MY post, I missed that you wanted for each
worker.Just add the parameter a2:a22="tom"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Deckbeers" wrote in message
...
That's great! It gets me the number for the first worker.

I'm trying to build a summary table, so how would I create those numbers
for
each worker? I could be an ID10T and have missed your point. Do I need
column
A in there somewhere?

"Don Guillett" wrote:

=SUMPRODUCT(--(B2:B22<"")*--(B2:B22TODAY()+7),--(C2:C22<""))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Deckbeers" wrote in message
...
Hello,

I need help figuring out a formula that counts the number of incomplete
jobs
for a specific worker that are within the next seven days. I would only
want
to count the "Date Completed" column if it was blank and within seven
days
from today's date. I've tried using sumproduct, but I can't seem to
figure
out how to get the date calculations to work properly.

My data looks similar to this

Worker Due Date Date Completed
Bob 1/18/2010 1/13/2010
Bob 1/21/2010
Bob 2/20/2010
Tom 1/20/2010
Tom 2/10/2010 1/10/2010

You help is much appreciated.

.

Thank you everyone for providing such great guidance!

I am getting the expected results now, and more importantly my boss will be
off my back for a few days.

"Deckbeers" wrote:

Hello,

I need help figuring out a formula that counts the number of incomplete jobs
for a specific worker that are within the next seven days. I would only want
to count the "Date Completed" column if it was blank and within seven days
from today's date. I've tried using sumproduct, but I can't seem to figure
out how to get the date calculations to work properly.

My data looks similar to this

Worker Due Date Date Completed
Bob 1/18/2010 1/13/2010
Bob 1/21/2010
Bob 2/20/2010
Tom 1/20/2010
Tom 2/10/2010 1/10/2010

You help is much appreciated.