Summing values based on criteria located in column and row headings

I have been struggling with finding the best way to easily find and sum data from a large database based on certain criteria. The database will contain client billing records, and will be divided into sheets based upon employee. Here is a screenshot of what the database looks like at this point:

EF screenshot.jpg

You'll notice that:

• Each sheet is devoted to one employee.
• The data is divided by client...
• ...and further divided by "Time Billed/Paid", "Dollars Billed/Paid" (many clients are new ventures, and unfortunately some fail. So, it is important to distinguish between "Billed" and "Paid").
• Finally, the rows display the date (not sure if that data should be displayed in one cell: "Jul-11" or two cells "July" "2011".
• I have been using Conditional Formatting to alert me of discrepancies between "Billed/Paid".

I want to be able to sum these values based upon the criteria I talked about above. For instance:

• Sum totals for the entire worksheet or a particular client (I know how to do that ).
• Sum for specific data, like if I want to know:
• 2011 for Client X.
• 2012 Q3 totals.
• comparison between "Billed" versus "Paid".

My intention for all this is to:

• An overview of how the company is doing.
• Trends in data, i.e. if we bill a startup company too much, too fast does it result in us never being paid? Or, what type of companies are we losing money on?
• To know how specific employees are performing:
• I am purchasing my partner's share of the company. Currently, we both receive a base salary, we then divide the additional revenue based upon our hours billed. Under the new scheme, he will receive additional compensation for his share, based upon two factors. 1) Overall revenue of the company, and 2) a higher percentage for certain clients.
• Employees are salaried, but receive overtime pay. I am changing this to a base salary plus a quarterly bonus based upon revenue brought in (not "Billed", but rather "Paid").
• Comparison between non-billable hours (client 99) and billable hours.
• Other uses I cannot think of right now.

My plan is to have several sheets, one for each employee, with all the data. This will be updated regularly. On a separate sheet I am hoping to create the formulas necessary to present the data. To sum column totals, I've used the sumif function. For instance to obtain the the total amount of "Time Billed" for the sheet.

Formula:

Please Login or Register  to view this content.

But I have never been successful using the sumifs

I've also summed based on fiscal quarters:

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

This was incredibly time consuming, and only gives me data for three months at a time.

I've tried several different formulas: vlookup, sumifs, dsum, to sum data based on column and row criteria, I have also explored arrays, all to no success. I'm sure there are ways to do all this, but they are apparently beyond me. I don't even know if I set up the database right, but I am very willing to change it if it makes the above possible.

Thanks so much for any insight you have! This is my first post, but I cannot tell you how many times I have used this forum in the past. It has been a lifesaver. Thanks to everyone who shares their time and knowledge on the site!

Attached:Billing Database (cleaned).xlsx

This is wonderful, thank-you for your response! You are correct in that the database needs to be created, that is, the data will be entered here, and not pulled from another source. I agree that I want the data and the output separated. I started experimenting with different formulas on that page to see how I would later pull the data I needed and ended up incorporating them into the data sheet. It definitely sounds like like my database is flawed?

I have started creating a sheet based upon your suggestions, I guess I am not clear on how the Metric column works? Thanks again!

EF2 screenshot.jpg