0

Intro: I am attempting to sum together some invoices in a rather large spreadsheet. Each row is a specific invoice for a particular company. I would like to sum the invoices for each company.

But here's the tricky bit.

Invoices/bills are submitted by different people. So John Smith might invoice Company ID: 1001 $50, then Sallie Mae might invoice Company ID: 1001 $200.

Ideally, I would like a table showing the total invoices from every person for each company, and then a column for the total amount billed for each company.

Question: What functions should I look at to accomplish this? I'm assuming that, due to the dynamic nature of the length of this particular document from month to month, I won't be able to do this straight in Excel. Is this something that I can do in Visual Basic?

Some Help: Check out this Google Doc to see some sample data, and the ideal output. https://docs.google.com/spreadsheet/ccc?key=0At5iFHDfTNkkdG8tUzRhaEQ1dFRqN0tWOEp0VmFpY0E&usp=sharing

  • 1
    Check out some Pivot Tables! – Raystafarian Feb 4 '14 at 20:37
  • yeah I second that. Do you have data regarding every person from each company in this spreadsheet as well? You mention thats how you want the data sorted but no mention of you actually having the data included in your spreadsheet. Pivot tables require data to be formatted in a very specific way in order to work correctly. Could you post a quick screenshot example to give us an idea of how the data is organized? – Richie086 Feb 4 '14 at 20:42
  • Sorry, I wasn't exactly clear. There are a finite number of people submitting invoices from MY company, not other companies. I want to see which of my employees is submitting invoices for each of our clients, and then a total of how much we billed that client. Data is sensitive, so I'll try and figure out a way to share some of it. – Jon Feb 4 '14 at 20:46
  • Check out this Google Doc to see some sample data, and the ideal output. docs.google.com/spreadsheet/… – Jon Feb 4 '14 at 20:52
3

@Raystafarian is correct to suggest using pivot tables. They are a very useful feature that will save you lots on time on projects like this. Here is a pivot table for your sample data that took about 30 seconds to create.

enter image description here

To create a pivot table, just select all your data (with headers), and choose Insert Pivot Table from the Insert ribbon (in Excel 2007 and later). After choosing a location for your pivot table, you must set up how your fields will be used in the table. In the Pivot Table menu that opens on the right side of the screen, just drag and drop the field names into the positions shown in the screen capture, and you're basically done. You can right click on the pivot table and adjust the pivot table settings (e.g., show blanks as 0, don't show totals for rows) to make your table look how you want.

Learn to use them, and the world is your oyster.

  • Wow, even I was able to figure it out in like 10 seconds. Thanks! – Jon Feb 5 '14 at 16:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.