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