Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a list of sales in an Excel file with vendor name and article price. This list is on one page, now I want to display the sum of all the sales by one person on a second page.

I have tried to vertical search for "name" in columns A to F on page 1 but that stops and shows the first result it sees, but not all the sales by that person. I tried to stick that same formula in =sum() but I never had any hope that would work really.

So my question would be, is there a way to search a column for a certain name and add up all the sales in the second column with that same name and display that on a second page.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

The fastest way to accomplish this is to convert your data into an Excel Table. Then, create a Pivot Table from your Data Table. Choose Vendor Name for your rows and Sum your Sales in the totals section.

Two bonuses with this. First, the Pivot subtotals can update as you update your data table. Second, you can group the dates for subtotals by week, month, quarter, year, etc...

Here's step-by-step directions (for Excel 2010, but they're very similar for Excel 2007).

  1. Convert your data into an Excel Table. Highlight a cell in your data and then Insert > Table.

  2. Create a Pivot Table from your data table. Highlight a cell in your new Data Table and then Insert > Pivot Table.

  3. Set-up your Pivot Table for your analysis. Highlight a cell in your Pivot Table and then choose the following:

    • Row Labels = Vendor Name

    • Values = Sales

Excel Table and Pivot

Your Values are now subtotaled by Vendor Name. You can have the table and pivot on the same or different worksheets, and you can manually refresh the pivot whenever the table changes, or have it update automatically.

From here, feel free to play around with it. You can add more criteria in your rows or columns for a more robust report.

share|improve this answer
    
exactly what i needed! Only i have one more question, is it possible with this solution to only add up sales that belong to a certain family? –  Graviton Feb 12 '13 at 20:56
    
I'm not quite sure what you mean by family. Generally, you can filter out results in either the table or pivot, so I'm thinking the answer is yes. You can also add another column to the table and to the pivot to either sub-subtotal or filter based on the additional value. An example would help to direct you more specifically. –  dav Feb 12 '13 at 21:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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