get a total based on criteria in two columns

Hi,

I am trying to get a total number of records based on criteria in two
different columns. The columns are: Date closed (data enetered as YYYY/MM/DD)
and status (where I am looking for "CLOSED", "REGISTERED" and "RENEWED").

I want to count the number of files that have been closed, registered and
renewed in a specific month of a specific year, the day is irrelivant.

Any help would be greatly appreciated.

There may be an easier formula but this worked for me.

{=SUM(IF(MONTH(A$2:A$7)=1,IF(YEAR(A$2:A$7)=2005,IF(B$2:B$7=I2,1,0))))}

Range A2:A7 is the date in YYYY/MM/DD format.
Range B2:B7 is the "Status" (REGISTERED, CLOSED, RENEWED)

I chose the month of January and the year of 2005 to be the count criteria.

In the column to the right of my formula, I entered the 3 statuses in cells I2:I4.
Formula pasted in cell H2 and dragged down to H4 to reference each status. This is an array formula so enter with Ctrl-Shift-Enter.

Regards,

Steve

=SUMPRODUCT(--(TEXT(A2:A200="yyyymmm")="2005Jan"),--(B2:B200="CLOSED"))

etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"rar" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I am trying to get a total number of records based on criteria in two
> different columns. The columns are: Date closed (data enetered as
YYYY/MM/DD)
> and status (where I am looking for "CLOSED", "REGISTERED" and "RENEWED").
>
> I want to count the number of files that have been closed, registered and
> renewed in a specific month of a specific year, the day is irrelivant.
>
> Any help would be greatly appreciated.