Number of records by Month that meet a specific requirement

I have been working on a spreadsheet for the last few days, making it much
better for the end user with all of your help with functions. Now that I
have Excel doing the work for the end user, he is wanting MORE.

The spreadsheet is doing some counts based on specific text in a column.
The formual below is looking for the start of the cell to begin with "AZ -"
and doesn't care what text comes after it. This works great, it tells me
that I have 19 rows that begin with this value.

=COUNTIF(NdDts_3Kywrds_WIQP_07Feb05!$J$6:$J$155, "AZ -*")

The other hurdle this discussion group helped me overcome was counting how
many of the rows have dates in specific months, which the following formula
does for me and gives me a value of 2.

=SUMPRODUCT(--(TEXT(NdDts_3Kywrds_WIQP_07Feb05!$H$6:$H$155,"mmm")="Jan"),--(NdDts_3Kywrds_WIQP_07Feb05!$H$6:$H$155<>""))

These work great! Now he wants me to combine the two and tell him how many
rows have the specific cell starting with "AZ -" and have a January date.

I have to repeat this for all 50 States and about 4 Canadian provinces to
determine when each of the items in the rows are due to be completed (by the
date) for each state/province.

One way

=SUMPRODUCT(--(TEXT(H6:H155,"mmm")="Jan"),--(H6:H155<>""),--(LEFT(TRIM(J6:J155),3)="AZ-"))

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"Keith Brown" <[email protected]> wrote in message
news:[email protected]...
>I have been working on a spreadsheet for the last few days, making it much
> better for the end user with all of your help with functions. Now that I
> have Excel doing the work for the end user, he is wanting MORE.
>
> The spreadsheet is doing some counts based on specific text in a column.
> The formual below is looking for the start of the cell to begin with
> "AZ -"
> and doesn't care what text comes after it. This works great, it tells me
> that I have 19 rows that begin with this value.
>
> =COUNTIF(NdDts_3Kywrds_WIQP_07Feb05!$J$6:$J$155, "AZ -*")
>
> The other hurdle this discussion group helped me overcome was counting how
> many of the rows have dates in specific months, which the following
> formula
> does for me and gives me a value of 2.
>
> =SUMPRODUCT(--(TEXT(NdDts_3Kywrds_WIQP_07Feb05!$H$6:$H$155,"mmm")="Jan"),--(NdDts_3Kywrds_WIQP_07Feb05!$H$6:$H$155<>""))
>
> These work great! Now he wants me to combine the two and tell him how
> many
> rows have the specific cell starting with "AZ -" and have a January date.
>
> I have to repeat this for all 50 States and about 4 Canadian provinces to
> determine when each of the items in the rows are due to be completed (by
> the
> date) for each state/province.