Formula Count dates multiple criteria

[GPearson]

I hope you can help. 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). I have formulas
that count how many are assigned to a person -- =COUNTIF('EI
Inventory'!M:M,"J Smith")+COUNTIF('EI Inventory'!N:N,"J Smith"). I have a
formula that counts how many entries per month were assigned to a person in
Column M -- =SUMPRODUCT((ISNUMBER(SEARCH("J
Smith",M:M)))*(A:A<=--"10/31/2009")*(A:A>--"10/01/2009")). I need a formula
that counts for both Column M and N. Please help.
Thanks!

 

[Jacob Skaria]

Try the below for the count for the month of Oct 2009 from both cols

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

If this post helps click Yes

 

[GPearson]

Thanks, this worked great.

I have an additional question. I need to add another criteria. The
spreadsheet has a column that records a completion date(column O) . I need
to use the same criteria as below but now I need to add what have not been
completed (column O is blank) and a seperate formula that shows the ones that
have been completed. So, I need a formula that will give me the total number
asssigned to a person for a month that have not been completed. In addition,
I need a formula that gives me the total number asssigned to a person for a
month that have been completed.

Thanks again!

 

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