| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count # of classes by month by local
Hey everyone: Excell 2007 on Vista
This one is getting to me and I"m hoping you can help. My current data layout: Col C Col G Col E Florida Word 6/1/09 Florida Excel 6/1/09 Cali Word 6/1/09 NJ Excel 6/2/09 NJ Excel 6/2/09 Cali PowerPoint 7/2/09 Florida Excel 6/3/09 PA Word 7/2/09 PA Outlook 7/2/09 Florida Outlook 8/1/09 What I have done so far on a different worksheet is calulcate (using an array formula) the number of total DAYS a location is running a class. The summary currently looks like this: LOCAL: # OF TOTAL DAYS: Florida 3 Cali 2 NJ 1 PA 1 NOW what I'd like to do is break down the number of days by each MONTH. Ultimately this new table would display something like the following: LOCAL: JUNE JUL AUG Florida 2 0 0 Cali 1 1 0 NJ 1 0 0 PA 0 2 0 I would prefer not to use a Pivot Table for this, and instead would just add these 3 new columns to the right of the "# OF TOTAL DAYS" column. Can anyone help?? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count # of classes by month by local
LOCAL: JUNE JUL AUG
Florida 2 0 0 Cali 1 1 0 NJ 1 0 0 PA 0 2 0 I'm assuming that's just for demonstration purposes only since the numbers don't jive with your posted raw data. On your summary sheet make sure you use a consistent format for the month name column header. In the above you have 1 long month name and 2 short month names. Make them *all the same*, either all long names or all short names. I'm going to assume the month name column headers are in the short name format mmm. LOCAL: # OF TOTAL DAYS: Florida 3 Cali 2 NJ 1 PA 1 Let's assume that data is on sheet2 in the range A1:B5 C1:E1 = month names = Jun, Jul, Aug as TEXT entries Enter this formula in C2: =SUMPRODUCT(--(Sheet1!$C$2:$C$11=$A2),--(TEXT(Sheet1!$E$2:$E$11,"mmm")=C$1)) Copy across to E2 then down to C5:E5 -- Biff Microsoft Excel MVP "Access Joe" wrote in message ... Hey everyone: Excell 2007 on Vista This one is getting to me and I"m hoping you can help. My current data layout: Col C Col G Col E Florida Word 6/1/09 Florida Excel 6/1/09 Cali Word 6/1/09 NJ Excel 6/2/09 NJ Excel 6/2/09 Cali PowerPoint 7/2/09 Florida Excel 6/3/09 PA Word 7/2/09 PA Outlook 7/2/09 Florida Outlook 8/1/09 What I have done so far on a different worksheet is calulcate (using an array formula) the number of total DAYS a location is running a class. The summary currently looks like this: LOCAL: # OF TOTAL DAYS: Florida 3 Cali 2 NJ 1 PA 1 NOW what I'd like to do is break down the number of days by each MONTH. Ultimately this new table would display something like the following: LOCAL: JUNE JUL AUG Florida 2 0 0 Cali 1 1 0 NJ 1 0 0 PA 0 2 0 I would prefer not to use a Pivot Table for this, and instead would just add these 3 new columns to the right of the "# OF TOTAL DAYS" column. Can anyone help?? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count # of classes by month by local
Hi,
Assume that your data is in range C5:G14. Assume that you have the four states in range C19:C22 and month names in D18:F18. In cell D19, enter the following array formula (Ctrl+Shift+Enter) =SUMPRODUCT(1*(FREQUENCY(IF($C$5:$C$14=$C19,IF(TEX T($E$5:$E$14,"mmmm")=D$18,IF($E$5:$E$14<"",MATCH( "~"&$E$5:$E$14,$E$5:$E$14&"",0)))),ROW($E$5:$E $14)-ROW($E$4))0)) You may now copy this down and across -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Access Joe" wrote in message ... Hey everyone: Excell 2007 on Vista This one is getting to me and I"m hoping you can help. My current data layout: Col C Col G Col E Florida Word 6/1/09 Florida Excel 6/1/09 Cali Word 6/1/09 NJ Excel 6/2/09 NJ Excel 6/2/09 Cali PowerPoint 7/2/09 Florida Excel 6/3/09 PA Word 7/2/09 PA Outlook 7/2/09 Florida Outlook 8/1/09 What I have done so far on a different worksheet is calulcate (using an array formula) the number of total DAYS a location is running a class. The summary currently looks like this: LOCAL: # OF TOTAL DAYS: Florida 3 Cali 2 NJ 1 PA 1 NOW what I'd like to do is break down the number of days by each MONTH. Ultimately this new table would display something like the following: LOCAL: JUNE JUL AUG Florida 2 0 0 Cali 1 1 0 NJ 1 0 0 PA 0 2 0 I would prefer not to use a Pivot Table for this, and instead would just add these 3 new columns to the right of the "# OF TOTAL DAYS" column. Can anyone help?? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count # of classes by month by local
Hi,
Assuming your Total Days column is column B, you don't need an array: =COUNTIF(Sheet1!C$2:C$11,Sheet2!A2) For the other formula use T. V.'s -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Access Joe" wrote: Hey everyone: Excell 2007 on Vista This one is getting to me and I"m hoping you can help. My current data layout: Col C Col G Col E Florida Word 6/1/09 Florida Excel 6/1/09 Cali Word 6/1/09 NJ Excel 6/2/09 NJ Excel 6/2/09 Cali PowerPoint 7/2/09 Florida Excel 6/3/09 PA Word 7/2/09 PA Outlook 7/2/09 Florida Outlook 8/1/09 What I have done so far on a different worksheet is calulcate (using an array formula) the number of total DAYS a location is running a class. The summary currently looks like this: LOCAL: # OF TOTAL DAYS: Florida 3 Cali 2 NJ 1 PA 1 NOW what I'd like to do is break down the number of days by each MONTH. Ultimately this new table would display something like the following: LOCAL: JUNE JUL AUG Florida 2 0 0 Cali 1 1 0 NJ 1 0 0 PA 0 2 0 I would prefer not to use a Pivot Table for this, and instead would just add these 3 new columns to the right of the "# OF TOTAL DAYS" column. Can anyone help?? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count # of classes by month by local
Joe
You need to have your month heading either in MMM format ie JUN,JUL or in MMMM format JUNE,SEPTEMBER etc; Assuming you need your new table starting in Col G1 as below: G1 LOCAL: JUN JUL AUG Florida = = = Cali = = = NJ = = = PA = = = in H2 enter the below formula (with month format in MMM) =SUMPRODUCT(--(TEXT($E$1:$E$20,"MMM")=H$1),--($C$1:$C$20=$G2)) in H2 enter the below formula (with month format in MMMM) =SUMPRODUCT(--(TEXT($E$1:$E$20,"MMMM")=H$1),--($C$1:$C$20=$G2)) If this post helps click Yes --------------- Jacob Skaria "Access Joe" wrote: Hey everyone: Excell 2007 on Vista This one is getting to me and I"m hoping you can help. My current data layout: Col C Col G Col E Florida Word 6/1/09 Florida Excel 6/1/09 Cali Word 6/1/09 NJ Excel 6/2/09 NJ Excel 6/2/09 Cali PowerPoint 7/2/09 Florida Excel 6/3/09 PA Word 7/2/09 PA Outlook 7/2/09 Florida Outlook 8/1/09 What I have done so far on a different worksheet is calulcate (using an array formula) the number of total DAYS a location is running a class. The summary currently looks like this: LOCAL: # OF TOTAL DAYS: Florida 3 Cali 2 NJ 1 PA 1 NOW what I'd like to do is break down the number of days by each MONTH. Ultimately this new table would display something like the following: LOCAL: JUNE JUL AUG Florida 2 0 0 Cali 1 1 0 NJ 1 0 0 PA 0 2 0 I would prefer not to use a Pivot Table for this, and instead would just add these 3 new columns to the right of the "# OF TOTAL DAYS" column. Can anyone help?? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count # of classes by month by local
Thank you ALL for your suggestions. I will give them each a try.
Can I ask one more thing? Given the same layout, I have ANOTHER column (H) that displays the class status. It'll either say "Active" or "Cancelled". Is there a way to count up the number of cancellations for each given location? Thank you again. Yous suggestions are sure to be VERY helpful. "Jacob Skaria" wrote: Joe You need to have your month heading either in MMM format ie JUN,JUL or in MMMM format JUNE,SEPTEMBER etc; Assuming you need your new table starting in Col G1 as below: G1 LOCAL: JUN JUL AUG Florida = = = Cali = = = NJ = = = PA = = = in H2 enter the below formula (with month format in MMM) =SUMPRODUCT(--(TEXT($E$1:$E$20,"MMM")=H$1),--($C$1:$C$20=$G2)) in H2 enter the below formula (with month format in MMMM) =SUMPRODUCT(--(TEXT($E$1:$E$20,"MMMM")=H$1),--($C$1:$C$20=$G2)) If this post helps click Yes --------------- Jacob Skaria "Access Joe" wrote: Hey everyone: Excell 2007 on Vista This one is getting to me and I"m hoping you can help. My current data layout: Col C Col G Col E Florida Word 6/1/09 Florida Excel 6/1/09 Cali Word 6/1/09 NJ Excel 6/2/09 NJ Excel 6/2/09 Cali PowerPoint 7/2/09 Florida Excel 6/3/09 PA Word 7/2/09 PA Outlook 7/2/09 Florida Outlook 8/1/09 What I have done so far on a different worksheet is calulcate (using an array formula) the number of total DAYS a location is running a class. The summary currently looks like this: LOCAL: # OF TOTAL DAYS: Florida 3 Cali 2 NJ 1 PA 1 NOW what I'd like to do is break down the number of days by each MONTH. Ultimately this new table would display something like the following: LOCAL: JUNE JUL AUG Florida 2 0 0 Cali 1 1 0 NJ 1 0 0 PA 0 2 0 I would prefer not to use a Pivot Table for this, and instead would just add these 3 new columns to the right of the "# OF TOTAL DAYS" column. Can anyone help?? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count # of classes by month by local
Try this:
=SUMPRODUCT(--(Sheet1!C$2:C$11=A2),--(Sheet1!H$2:H$11="Cancelled")) Copy down as needed. -- Biff Microsoft Excel MVP "Access Joe" wrote in message ... Thank you ALL for your suggestions. I will give them each a try. Can I ask one more thing? Given the same layout, I have ANOTHER column (H) that displays the class status. It'll either say "Active" or "Cancelled". Is there a way to count up the number of cancellations for each given location? Thank you again. Yous suggestions are sure to be VERY helpful. "Jacob Skaria" wrote: Joe You need to have your month heading either in MMM format ie JUN,JUL or in MMMM format JUNE,SEPTEMBER etc; Assuming you need your new table starting in Col G1 as below: G1 LOCAL: JUN JUL AUG Florida = = = Cali = = = NJ = = = PA = = = in H2 enter the below formula (with month format in MMM) =SUMPRODUCT(--(TEXT($E$1:$E$20,"MMM")=H$1),--($C$1:$C$20=$G2)) in H2 enter the below formula (with month format in MMMM) =SUMPRODUCT(--(TEXT($E$1:$E$20,"MMMM")=H$1),--($C$1:$C$20=$G2)) If this post helps click Yes --------------- Jacob Skaria "Access Joe" wrote: Hey everyone: Excell 2007 on Vista This one is getting to me and I"m hoping you can help. My current data layout: Col C Col G Col E Florida Word 6/1/09 Florida Excel 6/1/09 Cali Word 6/1/09 NJ Excel 6/2/09 NJ Excel 6/2/09 Cali PowerPoint 7/2/09 Florida Excel 6/3/09 PA Word 7/2/09 PA Outlook 7/2/09 Florida Outlook 8/1/09 What I have done so far on a different worksheet is calulcate (using an array formula) the number of total DAYS a location is running a class. The summary currently looks like this: LOCAL: # OF TOTAL DAYS: Florida 3 Cali 2 NJ 1 PA 1 NOW what I'd like to do is break down the number of days by each MONTH. Ultimately this new table would display something like the following: LOCAL: JUNE JUL AUG Florida 2 0 0 Cali 1 1 0 NJ 1 0 0 PA 0 2 0 I would prefer not to use a Pivot Table for this, and instead would just add these 3 new columns to the right of the "# OF TOTAL DAYS" column. Can anyone help?? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| count month when date is in day/month/year format | Excel Worksheet Functions | |||
| How to count in month? | Excel Worksheet Functions | |||
| Count By Month | Excel Discussion (Misc queries) | |||
| Counting Classes | Excel Discussion (Misc queries) | |||
| count by month | Excel Worksheet Functions | |||
Linear Mode
