Multiple Criteria Date Count formula

[GPearson]

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!

 

[T. Valko]

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<>""))