+ Reply to Thread
Results 1 to 4 of 4

Summing values based on criteria located in column and row headings

  1. #1
    Registered User
    Join Date
    12-30-2014
    Location
    Salt Lake City
    MS-Off Ver
    MS Office for Mac 2011 (vs. 14)
    Posts
    2

    Question 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: copy to clipboard
    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: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    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

  2. #2
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    MS OFFICE 2013
    Posts
    702

    Re: Summing values based on criteria located in column and row headings

    Before I jump in here. What is the output directly out of your database? Or are you stating that this is your database....

    I think the general layout is the issue with your workbook currently and you will find a much simpler process if you change the way you are storing your data.

    Is there a need to display every single client 99 147 etc... all at once, or would it not be better to pull the specific client as you wish to see them and have a summary of all without displaying every single client?

    What I would recommend is having a page called Data or Log in which contains records not reports.

    What I mean is that your headers would be Year,Quarter,Month,Date,Client,Metric, Amount.
    Metric would be the Time Billed, Time Paid, Dollars Billed, Dollars Paid

    With your data setup like this you can create a smaller summary page that you can cycle through time and clients instead of scrolling and searching for a client and time frame.

    DATE being the date you enter the record or in which it pertains to
    To get year just use
    Please Login or Register  to view this content.
    Month
    Please Login or Register  to view this content.
    Quarter
    Please Login or Register  to view this content.
    Now all of your formulas are a SUMIFS of the data in your log/Data sheet.

    Now this is all tentative pending your response on how this data is generated and added to the workbook. If what we see is the output directly from a system (Doubtful) but if so we can look into what to do with it... but currently it is in a format that will only cause you more pain in management and potential errors due to layout.

    If nothing else, answer where the data comes from and if it is necessary to display all of those clients in the manner you have currently.

    Cheers
    -If you think you are done, Start over - ELeGault

  3. #3
    Registered User
    Join Date
    12-30-2014
    Location
    Salt Lake City
    MS-Off Ver
    MS Office for Mac 2011 (vs. 14)
    Posts
    2

    Re: Summing values based on criteria located in column and row headings

    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

  4. #4
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    MS OFFICE 2013
    Posts
    702

    Re: Summing values based on criteria located in column and row headings

    Metric can be named anything you would like, but if you utilize those headings you had on your other sheet such as Time Billed, Time Paid, Dollars Billed, Dollars Paid then you can use these in formulas to pull back the information for a given date or time frame...

    For instance if you wanted to know how much time a client had been billed your formula would look something like this based on the image you shared...

    Please Login or Register  to view this content.
    I asked it to give me the total amount SUMIFS(G:G where it is the client designated and the month in C is december (12) and year in A is 2014 and the record is a Time Billed in column F....

    Now I dont recommend ever writing in the information you are looking for such as the clients name, the month (12) or year (2014)... these would all be fields you can enter to adjust without having to alter your formulas but that was to illustrate how you would use it... F:F "Time Billed" I called it metric but you can call it record type or anything meaningful that makes sense to you and the end users of the data.

    Fill out your data or at least 20 records or so in the new Data set format. Then make a second tab "Summary" and attempt to get what you need out of it - If you still find yourself struggling let us know and I or someone here can help shore up your formulas.

    Cheers,

    Happy New Year!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Summing Values in column based upon multiple criteria
    By shantanuk in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-21-2012, 09:29 AM
  2. Summing values based on two criteria
    By smartguy in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-16-2007, 04:46 PM
  3. Replies: 1
    Last Post: 08-02-2006, 12:15 PM
  4. Summing rows based on column headings
    By DallasLDY in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-08-2006, 05:42 PM
  5. Summing Values Based on Text Criteria
    By ExcelTip in forum Tips and Tutorials
    Replies: 4
    Last Post: 11-02-2005, 10:58 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1