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!

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
---------------
Jacob Skaria


"GPearson" wrote:

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!

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!

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"GPearson" wrote:

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!

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
...
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!

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"GPearson" wrote:

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!