Need to get sums based on codes in drop downs by week- Is pivot table right answer?

I am using Excel2003.
I need two distinct sums:
I need to calculate out the loan amount of approved loans by week (weekending on Sunday).
For example for the week ending December 28th, 2013
$1,234,567,890 in total loans

I also need to have a list of sums of the loan amounts per code types of approved loans.
For the week ending December 28th, 2013
$123,456.00 in total loans for Code: LTV/TLTV > Guideline max
$987,654.00 in total loans for Code: FICO > 10 pts below Guideline min


I need to ignore any loan amounts and codes for any loans that have not yet had a approved date entered and also ignore all loan amounts for any loans that are denied.

I am attaching my sheet for example of what I am working with.
Loan Amt by Week-Excpt Type.xls
I have tried doing this in many different ways with Pivot Tables and I am not getting the results I need.
I really appreciate any help anyone can give me. I am very new to doing these types of things in Excel.

try using sumifs()

I do not know what you mean. I am very new to this.

Using pivot table would work fine however you would need to arrange your date tables differently. I have used a pivot table for my profit loss reports similar to what I think you are trying to do with your spreadsheet. Not quite sure how you want it laid out but I will try to make a table based on the info you have given me and submit my idea back.

OK. I'll send you a sample with your data

Could you please post with just some raw data and nothing else on the spreadsheet so I can see what I can work with?

Okay looking at what you have it will work in a pivot table the trick is organizing the data to read easily for the table to pick up on it. Ill mess with a few things and see if I can come up with a table that might work well for you.

This is exactly what I needed.
But how do I get it to automatically do this in a different worksheet of the workbook. Normally the dropdowns worksheet is protected and hidden but for this example I left them open so you could see what I was doing.
I need it to automatically update and create this every week?
I normally would not have access to this file on regular basis, and the people who use it barely know how to use excel, so the more automated it is the better.

If you are referring to post #5 then the dates can change based on the reporting month

Originally Posted by rcm

If you are referring to post #5 then the dates can change based on the reporting month

Yes, but how will it do that on its own? I am lost here.

OK. Help me fully understand the dynamics..

1. It can be arranged that the reported weeks are the last 4 or 5 weeks of the latest reported month. Since Jan was posted the weeks of January were reported.
2. Are there a fixed amounts of similar tabs or do they spring up as needed.?
3. Do Summaries have to include all tabs or is there a summary for each tab?
4. Following #3, are there other workbooks involved?

Originally Posted by rcm

OK. Help me fully understand the dynamics..

1. It can be arranged that the reported weeks are the last 4 or 5 weeks of the latest reported month. Since Jan was posted the weeks of January were reported.
2. Are there a fixed amounts of similar tabs or do they spring up as needed.?
3. Do Summaries have to include all tabs or is there a summary for each tab?
4. Following #3, are there other workbooks involved?

1. I need the info calculated by each week for every month.
2. The info in the Tab January is entered in daily as it come in. A new worksheet is created for every month of the year but because of the size of it we create a new workbook every quarter.
3. This summary info just needs to be calculated based on the info from that months Tab, in this instance it is January.
4. As I answered in #2, each quarter a new workbook is created from a master workbook and the Tab is renamed for that Month. At the end of the year all the months are compiled together into one workbook for the year.

Actually in relooking at the calculations that were created in the worksheet that RCM made, it is not calculating it right.
Cell D4 on the Dropdowns worksheet should be $320,000: as there was one loan approved at $20,000 and another at $300,000 that were both approved on 1/3/14 with a code of LTV/TLTV > Guideline max.

Thanks Cdavies, Ill look into it

There can be up to 3 codes for each approval. Most of the time there will be one or two but sometimes 3. All three have to be accounted for in my report.