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