Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Keith Brown
 
Posts: n/a
Default 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.
  #2   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to extract month number from month name PM Excel Discussion (Misc queries) 2 January 19th 05 04:07 PM
Convert week number into calendar month? WickyWick Excel Worksheet Functions 2 November 9th 04 10:01 PM
Returning the Week Number of a Specific Date on a Month arjcvg Excel Worksheet Functions 0 November 5th 04 02:21 AM
Returning the Week Number of a Specific Date on a Month arjcvg Excel Worksheet Functions 1 November 3rd 04 05:35 AM
Returning the Week Number of a Specific Date on a Month arjcvg Excel Worksheet Functions 0 November 3rd 04 05:26 AM


All times are GMT +1. The time now is 04:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"