 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / November 2006How do I count frequency based on 2 criteria (including month)
Hi everyone. I've spent quite a few hours looking all over the internet and within this community for the answer to my question. While there are various solutions out there [using SUMPRODUCT for example (which I've never used)], I can't seem to find one that specifically addresses my issue (I'm sure it must be out there but I just can't find it). Here's the situation my client wants: "...is it possible to track the number of program types on a monthly basis?" In the spreadsheet, there's a column with closing dates (data starts in J49 on down; format for dates is m/d/y, example: 7/31/06) and another with program types (starting in K49 on down; example: Home). The programs types come from a list of 10 choices located in cells AC14:AC23. I'm trying to create a separate table on a different worksheet with months as the column headings and the 10 program types as the row headings. Here is part of the new table: Jul Aug Sep Oct Nov Dec Home - - - - - - Kin - - - - - - FC - - - - - - IFC - - - - - - GH - - - - - - IL - - - - - - Res - - - - - - Hosp - - - - - - STARR - - - - - - Other - - - - - - I tried creating a formula to do this but I'm having some problems. Here is what I tried for tracking "Home" in the month of July: IF(MONTH(Worksheet!$J$49:Worksheet!$J$969,7),COUNTIF(Worksheet!$K$49:Worksheet!$K$969,Worksheet!$AC$14)) Even though there are currently only 20 or so rows filled in I used J969 to make sure the formula went far enough down. I know that Excel automatically extends formulas but I didn't know if it would also do it for calculations already in the spreadsheet [ex: would sum(M49:M69) or in this case (MONTH(J49:J69))be automatically extended to include additional rows of data as they were added?]. I know that I would need to change the absolute reference from $AC$14 to $AC14 when copying the forumlas down the table to include the other programs. I figure that rather than wasting any more hours (already have spent many hours) searching for a solution all over the web, I would post my question to all the experts in this community. I'm sure someone with much greater expertise than I have should be able to solve my problem fairly easily. Thank you once again and sorry if this solution has been answered before (couldn't find it). See this screencap: http://img261.imageshack.us/img261/3509/sumproductjn1.jpg Enter the formula in N50 then copy across then down. Biff > Hi everyone. I've spent quite a few hours looking all over the internet > and [quoted text clipped - 57 lines] > easily. Thank you once again and sorry if this solution has been answered > before (couldn't find it). Dear Biff, Thanks for the suggestion. I tried using the formula you suggested but it was returning a zero value for all the results. I think it was because the months in my table refer to another cell which is actually a date (custom formated so as to display only the month). Where B$36 refers to a cell in the Worksheet with the following formula: =DATE(YEAR($H$2)-1,7,1) and the format is Custom (mmm). In my case, H2 = 6/30/07. Also, Worksheet!$AC14 is the program name "Home". This your formula as I tried it: =SUMPRODUCT(--(TEXT(Worksheet!$J$49:$J$70,"mmm")=B$36),--(Worksheet!$K$49:$K$70=Worksheet!$AC14)) The other thing that I also noticed is that if the closing dates are from a different fiscal year, the formula would also include those months also. So, I guess I need to have a formula which takes into account the month and year refered to in cell B$36. > See this screencap: > [quoted text clipped - 65 lines] > > easily. Thank you once again and sorry if this solution has been answered > > before (couldn't find it). >Thanks for the suggestion. I tried using the formula you suggested but it >was returning a zero value for all the results. I think it was because the >months in my table refer to another cell which is actually a date (custom >formated so as to display only the month). Yep, that's a problem! >The other thing that I also noticed is that if the closing dates are from a >different fiscal year, the formula would also include those months also. >So, >I guess I need to have a formula which takes into account the month and >year >refered to in cell B$36. Yep, that's another problem! Try this: =SUMPRODUCT(--(TEXT(Worksheet!$J$49:$J$70,"mmmyyyy")=TEXT(B$36,"mmmyyyy")),--(Worksheet!$K$49:$K$70=Worksheet!$AC14)) Biff > Dear Biff, > [quoted text clipped - 101 lines] >> > answered >> > before (couldn't find it). Dear Biff, Sorry for the delay in my response. Since I'm new at using this forum, I didn't know if answering yes would close this thread and not allow me to respond to the other posts (hopefully it won't after this reply). Anyway...the update you provided to your formula works like a charm!! I tested it by changing a closing date for one of the "Home" programs from 7/31/06 to 7/31/07 (which would be in the next fiscal year) and the formula removed it's occurrence from the current fiscal year's table. Once again, thank you so much for your quick solution to my problem!! > >Thanks for the suggestion. I tried using the formula you suggested but it > >was returning a zero value for all the results. I think it was because the [quoted text clipped - 123 lines] > >> > answered > >> > before (couldn't find it). You're welcome. Thanks for the feedback! Biff > Dear Biff, > [quoted text clipped - 161 lines] >> >> > answered >> >> > before (couldn't find it). =SUMPRODUCT((MONTH($J$49:$J$969)=7)*($K$49:$K$969="home")) or =SUMPRODUCT(--(MONTH($J$49:$J$969)=7),--($K$49:$K$969="home")) Regards Trevor > Hi everyone. I've spent quite a few hours looking all over the internet > and [quoted text clipped - 57 lines] > easily. Thank you once again and sorry if this solution has been answered > before (couldn't find it). Dear Trevor, Sorry for the delay in my reply, but after spending >30 minutes organizing & composing my reply, when I hit the post button it deleted everything I had typed and asked me to sign back in. Anyway, thanks for your help. This is your formula as I tried it: =SUMPRODUCT((MONTH(Worksheet!$J$49:$J$73)=7)*(Worksheet!$K$49:$K$73=Worksheet!$AC14)) where Worksheet!$AC14 is the program name "Home". The formula worked, but one thing I noticed was that if the closing dates are from a different fiscal year, the formula would also include those months also. So, I guess I need to have a formula which takes into account the month and year also. In my table on a separate worksheet called (FY07 Table), the column headings are months that actually refer to cells in the data-containing worksheet (called Worksheet). For example, the month of July (B36) in this summary table has the formula: =Worksheet!M5 where Worksheet!M5 is also July and has the following formula: =DATE(YEAR($H$2)-1,7,1) and the format is Custom (mmm). In my case, H2 = 6/30/07. Since my fiscal year starts runs from July 06 - June 07. The formula for the months Jan 07 - Jun 07 follow the format: for Jan 07: =DATE(YEAR($H$2)-1,13,1) and for Jun 07: =DATE(YEAR($H$2)-1,18,1). If I'm going to use years also, would I use 13-18 or 1-6 for the months? Thanks for your input. > =SUMPRODUCT((MONTH($J$49:$J$969)=7)*($K$49:$K$969="home")) > [quoted text clipped - 67 lines] > > easily. Thank you once again and sorry if this solution has been answered > > before (couldn't find it). Dear Trevor, Hopefully you will see this reply. Biff provided a solution to my problem, but since Biff's answer uses a slightly different formula than yours, and in the interest of broadening my Excel skills, how would you change your formula to answer the issues in my previous reply (namely including the year in the formula). Also, in my original post I asked "I know that Excel automatically extends formulas but I didn't know if it would also do it for calculations already in the spreadsheet [ex: would sum(M49:M69) or in this case (MONTH(J49:J69))be automatically extended to include additional rows of data as they were added?]. > Dear Trevor, > [quoted text clipped - 95 lines] > > > easily. Thank you once again and sorry if this solution has been answered > > > before (couldn't find it). Something like: =SUMPRODUCT(($J$49:$J$73>=DATE(2006,7,1))*($J$49:$J$73<=DATE(2007,6,30))*(MONTH($J$49:$J$73)=7)*($K$49:$K$73=$AC14)) Regards Trevor > Dear Trevor, > [quoted text clipped - 116 lines] >> > answered >> > before (couldn't find it). Dear Trevor, Thanks for the additional response! > Something like: > [quoted text clipped - 124 lines] > >> > answered > >> > before (couldn't find it). Would you consider letting a Pivot Table do the heavy lifting? Try this: <Data><Pivot Table> Use: Excel Select your data Click the [Layout] button ROW: Drag the ClosingDate field here COLUMN: Drag the ProgramType field here DATA: Drag the ProgramType field here If it doesn't list as Count of ProgramType...dbl-click it and set it to Count (also...you can rename "Count of ProgramType" to something more appropriate) Click [OK] Select where you want the Pivot Table...and Click the [Finish] button (Note: I'm having you temporarily put the ClosingDates on the left in case there are more than 256...Excel's column limit.) That will list ProgramTypes across the top ClosingDates down the left and the count of ProgramTypes. (not quite there yet...but continue reading) Right-click on the ClosingDate field Select: Group and Show Detail Group by: Months Click [OK] Now the left column displays Jan, Feb, Mar...etc...instead of dates. After they're grouped, you can drag the ClosingDate field to the top and drag the ProgramType to the left To refresh the Pivot Table, just right click it and select Refresh Data Is that something you can work with? *********** Regards, Ron XL2002, WinXP > Hi everyone. I've spent quite a few hours looking all over the internet and > within this community for the answer to my question. While there are various [quoted text clipped - 47 lines] > easily. Thank you once again and sorry if this solution has been answered > before (couldn't find it). Dear Ron, Sorry for the delay in this reply. Although Biff has provided an answer to my question, I wanted to go ahead and try to use the PivotTable feature you were talking about (I've never used it before). I followed the steps you outlined, although I had to make a modification by copying the program types from cells AC14-AC23 to an adjacent column next to the data-containing table in my Worksheet. Having done this, I followed your instructions but couldn't continue past the following steps: Right-click on the ClosingDate field Select: Group and Show Detail Group by: Months Click [OK] Now the left column displays Jan, Feb, Mar...etc...instead of dates. I'm using Excel 2000 so this might account for some of the differences. When I right-click on the ClosingDate field, I have a "Group and Outline" option which expands to include 4 options (summarized as either Hide/Show Detail and Group/Ungroup). When I choose "Show Detail", it gives me a list of choices, and selecting a month simply displays that month and all the info for that month. Repeating this to show multiple months and then trying to highlight the months and select "Group" didn't work. If I choose "Group" I get an error message saying "Cannot group that selection." How do I fix this? Also, I noticed at the top of page (Row 1) there is a blue outlined box going the length of the table that says "Drop Page Fields Here." What does this do? > Would you consider letting a Pivot Table do the heavy lifting? > [quoted text clipped - 91 lines] > > easily. Thank you once again and sorry if this solution has been answered > > before (couldn't find it). Hi If you are having problems with Grouping your Dates by Month, it either means that some cells within the range are not true Excel dates, or are blank cells. The PT will not do the grouping unless every cell contains a valid date. If you have made the overall range in your original selection of data, longer than that currently used to allow for more data being added, that could be the problem. Limit the range to just the used area or create a Dynamic range for your data (for help on this see below) The Page area, is one of 3 different areas to which you can allocate fields (columns) from your raw data. For more help on setting up and using Pivot tables go to Debra Dalgleish's site http://www.contextures.com/tiptech.html and scroll to the section on Pivot Tables and for Dynamic Range naming http://www.contextures.com/xlNames01.html#Dynamic or Mike Alexander's site http://www.datapigtechnologies.com/ExcelMain.htm http://www.datapigtechnologies.com/flashfiles/pivot1.html Do persist with getting to grips with Pivot Tables; you will find them invaluable.  SignatureRegards Roger Govier > Would you consider letting a Pivot Table do the heavy lifting? > [quoted text clipped - 118 lines] >> answered >> before (couldn't find it). Dear Roger, Thanks for your response. You're right in that my client has some blank cells in the closing date column hence I can't group the cells by month, although the PivotTable does generate a row at the bottom called (blanks). I also just quickly checked the links which you provided and found them to be incredibly useful. Coincidentally (& for the first time), I had found a solution to a autofiltering and password protection problem just a couple of days ago from Debra Dalgleish's Contextures website. She provided a solution that many other posts in different forums (Google groups & even Microsoft's official help topic on this issue!) didn't solve. Also, the link to Mike Alexander's DataPig Excel Training site is absolutely incredible! I checked out his info on PivotTable grouping and when I clicked on his link, there was a Flash video tutorial explaining exactly how to do that!! As much as I don't mind reading instructions on how to do something, I feel that watching a video of what needs to be done is much more effective and SO MUCH QUICKER than reading those same instructions. Thank you so much for your assistance (ESPECIALLY the 2 links that you provided)!!!!!! > Hi > [quoted text clipped - 147 lines] > >> answered > >> before (couldn't find it). Hi Glad you found the links useful. All the thanks though is really due to them for providing us all with such valuable resource. I usually get around the problem of blank dates by making the blanks a date way into the future, rather than blank. Then, you can use Grouping by Date, but set the range of dates to Exclude the far distant dates. SignatureRegards Roger Govier > Dear Roger, > [quoted text clipped - 210 lines] >> >> answered >> >> before (couldn't find it). Dear Roger, Although the blank tip you provided wouldn't be possible for my client to use, it is a useful tip that I could use sometime in the future as I learn more about pivot tables. Thanks again for those links. > Hi > [quoted text clipped - 220 lines] > >> >> answered > >> >> before (couldn't find it). > Hi everyone. I've spent quite a few hours looking all over the internet and > within this community for the answer to my question. While there are various [quoted text clipped - 47 lines] > easily. Thank you once again and sorry if this solution has been answered > before (couldn't find it). > RS I had to count base on a date range and found if I used a formula array by putting in my formula then pressing CRTL+SHFT+ENTER I got the correct count. This is the formula I used to count all dates between Oct 1 2006 and Oct 31 2006 finding the =DateValue(10/10/2006) you can plug in your own critera with ="Home" and date maybe this will help you out not sure just learning myself. =SUM(IF('Local Annual Sales'!B4:B19>=38991,IF('Local Annual Sales'!B4:B19<=39022,1,0),0)) Dear hlpmelrn, Glad to hear that you found a solution that worked for you. The solution that Biff provided (see the post dated 10/19/2006 3:29 PM PST) had a number of advantages for me. One, rather than me having to manually adjust the formula for each month, I could simply copy and paste it across the entire table and it would do so by itself. Two, since my formula refers to a date in my spreadsheet, not only does it exclude ranges not in the current fiscal year, but when the fiscal year changes, it will account for that and automatically update the table without me having to go in and manually adjust the formulas. > > Hi everyone. I've spent quite a few hours looking all over the internet and > > within this community for the answer to my question. While there are various [quoted text clipped - 51 lines] > =SUM(IF('Local Annual Sales'!B4:B19>=38991,IF('Local Annual > Sales'!B4:B19<=39022,1,0),0)) |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.