If/And/Vlookup question

[hoosonfirst]

Hi all,

I have a spreadsheet set up as follows:
Column A = dates in month/day/year format
Column E = number of hours
Column F = $ rate associated with the hours in Column E
Column I = Total of Column E X Column F

In Column J I would like to do the following, but I'm not sure what'
the most efficient way of doing it. I would like to summarize th
Total amount in Column I for each month and year. For example, I woul
like to add the totals from Column I for all the data for the month o
May, 2004 (dates in Column A). I understand I can add a row unde
each each month and subtotal the months that way, but I would prefer t
do this seperate from the original data in Columns A-I.

Is this possible?

Thanks

 

[Dave Peterson]

First, I think it makes life much simpler to use the features built into excel.

If you add another column, you could put:
=text(a2,"yyyymm")

And do data|subtotals based on this column.

Or you could use data|pivottable (against that column) or group by month and
year in the pivottable.

But this seemed to work ok in my test:

=IF(LOOKUP(2,1/(TEXT($A$2:$A$20,"yyyymm")=TEXT(A2,"yyyymm")),$A$2:$A$20)<>A2,
"",SUMPRODUCT(--(TEXT($A$2:$A$20,"yyyymm")=TEXT(A2,"yyyymm")),$I$2:$I$20))

(all one cell)

I had my test data in A2:I20 (with headers in Row 1). Change that range to
match your data.

And copy down the column.

 

[hoosonfirst]

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!

 

[Dave Peterson]

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/assistance/2002/articles/xlconPT101.aspx