Multiple Criteria Date Count formula
 

Hello!
I need further assistance with my previous post. My prior question was the
following:
I have a spreadsheet that records entry by date added (column A). Each
entry can be assigned to at least 1 person (column M). However, there are
times when an entry can be assigned to 2 people. The second entry is in
column N. Both column M and N use the same drop down lists. I need to count
how many items received each month that were assigned to a person (who could
be listed in either column M or N).
Heres the solution:
=SUMPRODUCT((TEXT(A1:A100,"mmyyyy")="102009")*(M1: N100="J Smith"))

Unfortunately, I need to add other criteria to the formula. My other
criteria is: The spreadsheet has a column that records a completion
date(column O). I need a formula that will give me the total number assigned
to a person for a month that have not been completed. In addition, I need a
formula that gives me the total number assigned to a person for a month that
have been completed.

Thanks for any help you can give!

Multiple Criteria Date Count formula
 

Try these...

Not completed:

=SUMPRODUCT((TEXT(A1:A100,"mmyyyy")="102009")*(M1: N100="J
Smith")*(O1:O100=""))

Completed:

=SUMPRODUCT((TEXT(A1:A100,"mmyyyy")="102009")*(M1: N100="J
Smith")*(O1:O100<""))

--
Biff
Microsoft Excel MVP


"GPearson" wrote in message
...
Hello!
I need further assistance with my previous post. My prior question was
the
following:
I have a spreadsheet that records entry by date added (column A). Each
entry can be assigned to at least 1 person (column M). However, there are
times when an entry can be assigned to 2 people. The second entry is in
column N. Both column M and N use the same drop down lists. I need to
count
how many items received each month that were assigned to a person (who
could
be listed in either column M or N).
Here's the solution:
=SUMPRODUCT((TEXT(A1:A100,"mmyyyy")="102009")*(M1: N100="J Smith"))

Unfortunately, I need to add other criteria to the formula. My other
criteria is: The spreadsheet has a column that records a completion
date(column O). I need a formula that will give me the total number
assigned
to a person for a month that have not been completed. In addition, I need
a
formula that gives me the total number assigned to a person for a month
that
have been completed.

Thanks for any help you can give!