Home |
Search |
Today's Posts |
#1
|
|||
|
|||
If/And/Vlookup question
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's the most efficient way of doing it. I would like to summarize the Total amount in Column I for each month and year. For example, I would like to add the totals from Column I for all the data for the month of May, 2004 (dates in Column A). I understand I can add a row under each each month and subtotal the months that way, but I would prefer to do this seperate from the original data in Columns A-I. Is this possible? Thanks! -- hoosonfirst ------------------------------------------------------------------------ hoosonfirst's Profile: http://www.excelforum.com/member.php...o&userid=26456 View this thread: http://www.excelforum.com/showthread...hreadid=397251 |
#2
|
|||
|
|||
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 wrote: 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's the most efficient way of doing it. I would like to summarize the Total amount in Column I for each month and year. For example, I would like to add the totals from Column I for all the data for the month of May, 2004 (dates in Column A). I understand I can add a row under each each month and subtotal the months that way, but I would prefer to do this seperate from the original data in Columns A-I. Is this possible? Thanks! -- 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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Newbie With A Question | Excel Worksheet Functions | |||
Anybody Help with previous question | Excel Discussion (Misc queries) | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
Question about combining data from multiple workbooks into one rep | Excel Discussion (Misc queries) | |||
An easy macro question and one I believe to be a little more diffi | Excel Worksheet Functions |