Count # of classes by month by local

[Access Joe]

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??

 

[T. Valko]

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

 

[Ashish Mathur]

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(TEXT($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

 

[Shane Devenshire]

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

 

[Jacob Skaria]

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

 

[Access Joe]

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.

 

[T. Valko]

Try this:

=SUMPRODUCT(--(Sheet1!C$2:C$11=A2),--(Sheet1!H$2:H$11="Cancelled"))

Copy down as needed.