Once you get that value in column J, you can use that as an indicator for the
other columns.
I put this in L2 (and dragged down):
=IF(J2="","",TEXT(A2,"yyyy_mmm"))
(Change the format to whatever you like.)
I put this in M2 (and dragged down):
=IF(J2="","",SUMPRODUCT(--(TEXT($A$2:$A$20,"yyyymm")=TEXT(A2,"yyyymm")),
$E$2:$E$20))
(still all one cell)
And this formula (very, very similar to M2) in N2 (and dragged down):
=IF(J2="","",SUMPRODUCT(--(TEXT($A$2:$A$20,"yyyymm")=TEXT(A2,"yyyymm")),
$i$2:$i$20))
(still all one cell.)
===
But you'd be amazed how fast you could get these statistics with a pivottable.
You spend an hour just playing with one and you'll be wondering how you ever got
anything done before.
To read more about the pivottable stuff, you may want to look at some links:
Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html
John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)
Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm
MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx
hoosonfirst wrote:
Dave,
Thank you very much for your reply. Your recommendations worked
exactly as described. Unfortunately, I was not completely clear. I'm
hoping to provide the summary data in the following format to do
additional analysis. With exactly the same data in the columns that I
detailed in my original post, I would like to add the following summary
in these columns...
Column L = Month/Year
Column M = Sum of Hours (from Column E) associated with the Month/Year
in L
Column N = Sum of Total $s (from Column I) associated with the
Month/Year in L
Is this possible with some excel formulas? I'm not familiar enough
with pivot tables to get this format to do additional analysis.
Thanks again for your help!
--
hoosonfirst
------------------------------------------------------------------------
hoosonfirst's Profile: http://www.excelforum.com/member.php...o&userid=26456
View this thread: http://www.excelforum.com/showthread...hreadid=397251
--
Dave Peterson