Morning All,

I need assistance with adding a condition to my "month counting" formula. I currently have two formulas that work (see below). I would like for the formula to only count months that have a status other than 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 one 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
Tee

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hey,

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

Got it!
Just added ($A$1:A$251<"") back into the formula.

SUMPRODUCT(--($A$1:A$251<""),--(MONTH($A$1:A$251)=1),--($E$1:$E$251<"Closed"))

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2