Counting # of entries for each month, in a specific status

[Tee51]

Morning All,

I need assistance with adding a condition to my "month counting
formula. I currently have two formulas that work (see below). I woul
like for the formula to only count months that have a status other tha
closed. Any ideas? Feel free to create a new formula if need be.


Example:
10/12/2012 ABC/13 ACCT4 1112227 Studio
10/30/2012 ABC/13 ACCT2 1112227 Closed
12/3/2012 ABC/13 ACCT5 1112228 Pending
2/12/2013 ABC/13 ACCT6 1112229 Waiting

The count for October is 2 but I want the count to show as 1 because on
of entries is closed.


SUMPRODUCT(--($A$1:A$251<>""),--(MONTH($A$1:A$251)=1))
SUMPRODUCT((TEXT($A$1:$A$1999,"mm")="01")+0,($A$1:$A$1999<>"")+0)

Thanks in advance
Te

 

[Claus Busch]

Hi,

Am Wed, 18 Jul 2012 12:33:56 +0000 schrieb Tee51:

10/12/2012 ABC/13 ACCT4 1112227 Studio
10/30/2012 ABC/13 ACCT2 1112227 Closed
12/3/2012 ABC/13 ACCT5 1112228 Pending
2/12/2013 ABC/13 ACCT6 1112229 Waiting

The count for October is 2 but I want the count to show as 1 because one
of entries is closed.

try:
=SUMPRODUCT(--(MONTH(A1:A251)=10),--(E1:E251<>"Closed"))


Regards
Claus Busch

 

[Tee51]

Hi,

Am Wed, 18 Jul 2012 12:33:56 +0000 schrieb Tee51:
-

try:
=SUMPRODUCT(--(MONTH(A1:A251)=10),--(E1:E251<>"Closed"))


Regards
Claus Busch

Hey,

Works great but it counts all the blank cells as January which distort
the count

 

[Claus Busch]

Hi,

Am Wed, 18 Jul 2012 20:37:24 +0000 schrieb Tee51:

Works great but it counts all the blank cells as January which distorts
the count.

try:
=SUMPRODUCT(--(YEAR(A1:A251)=2012),--(MONTH(A1:A251)=1),--(E1:E251<>"Closed"))


Regards
Claus Busch