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" wrote in message
...
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.