Counting w/ Multiple Criteria (Dates&Text)

[Matt]

I have a counting problem. I have a spreadsheet of
accounts with columns for Status (which equals Active or
Closed), create_dt (for date created in date format), and
closed_dt (for date closed in closed format). Some of
the date cells have [Null] as a value.

I want to count the number of accounts that were opened
in 1996 and closed in 1997. I was trying to use an array
formula, and I'd prefer that the 1996 & 1997 be
referenced cells. Here was what I was trying:

={sum((year(create_dt)=A1)*year(closed_dt)=A2))}
The function returns the #VALUE error.

Any suggestions?

Thanks,
Matt

 

[Anon]

I have a counting problem. I have a spreadsheet of
accounts with columns for Status (which equals Active or
Closed), create_dt (for date created in date format), and
closed_dt (for date closed in closed format). Some of
the date cells have [Null] as a value.

I want to count the number of accounts that were opened
in 1996 and closed in 1997. I was trying to use an array
formula, and I'd prefer that the 1996 & 1997 be
referenced cells. Here was what I was trying:

={sum((year(create_dt)=A1)*year(closed_dt)=A2))}
The function returns the #VALUE error.

Any suggestions?

Thanks,
Matt

I suggest you use SUMPRODUCT rather than SUM as an array formula, as this
eliminates any problems with array-entering. You will also need to use
absolute references for the years. So
=SUMPRODUCT((YEAR(create_dt)=$A$1)*(YEAR(closed_dt)=$A$2))

A1 and A2 will each need to contain a number (1996 and 1997), not a date
formatted as yyyy.

 

[Tom Ogilvy]

do your create_dt range or closed_dt range contain all dates or might there
be a text entry in the range (such as a column header). If so, this could
be the source of your error.

Regards,
Tom Ogilvy

 

[tammuz]

thanx anon, the SUMPRODUCT is really good...
anyway here's what i wrote before reading ur post
(i think SUMPRODUCT is rather static in terms of adding
new records..)
-----------------------------------------------

dear matt,

through ur post i figured out this sample database:

account status create_dt closed_dt forumula
--------------------------------------------------------
1210003352 closed 2/28/1996 7/8/1997 XXXXXXXX
1210004579 active 8/31/1995 XXXXXXXX
1210003997 active 6/15/1994 XXXXXXXX
1210002185 closed 9/12/1996 12/31/1997 XXXXXXXX
1210007899 closed 3/20/1998 4/30/1999 XXXXXXXX
1210001118 active 5/5/1995 XXXXXXXX
1210000788 active 10/12/1996 XXXXXXXX

[result]

now the formula column is an auto-fill of the formula:

=IF(AND(create_dt<>"",closed_dt<>""),AND
(status="closed",YEAR(create_dt)=1996,YEAR(closed_dt)
=1997),"")

figures 1996 and 1997 can be changed into fixed references
using dollar signs..

this is not an array formula to be more dynamic if data is
added later, and it will show TRUE for any account that
was activated in 1996 and closed in 1997..
now in the [result] cell we will place the formula :

=COUNTIF(formula_column,TRUE)

this will count the TRUE cases in the formula column,
which will be the result to ur question,,,

thanx..

done using EXCEL XP 2002


tammuz

damascus, syria

-----Original Message-----

I have a counting problem. I have a spreadsheet of
accounts with columns for Status (which equals Active or
Closed), create_dt (for date created in date format), and
closed_dt (for date closed in closed format). Some of
the date cells have [Null] as a value.

I want to count the number of accounts that were opened
in 1996 and closed in 1997. I was trying to use an array
formula, and I'd prefer that the 1996 & 1997 be
referenced cells. Here was what I was trying:

={sum((year(create_dt)=A1)*year(closed_dt)=A2))}
The function returns the #VALUE error.

Any suggestions?

Thanks,
Matt

I suggest you use SUMPRODUCT rather than SUM as an array formula, as this
eliminates any problems with array-entering. You will also need to use
absolute references for the years. So
=SUMPRODUCT((YEAR(create_dt)=$A$1)*(YEAR(closed_dt)=$A$2))

A1 and A2 will each need to contain a number (1996 and 1997), not a date
formatted as yyyy.


.