In this article we will be looking at how to create a report with Excel using INDEX, MATCH and SUMIF.
Let’s assume that we have a Liqueur firm that has two reports that have been downloaded from another application, Microsoft Nav. click here for further details.
The first file is a Posted memo file
The second file is a master file with customer information
To analyse this data
- On the Posted Memo file; Right-click on the B column and select Insert to insert a new column
- To add the sales person name to the file we will use the index/match formula
The index is what you want to add to the formula, from the customer sheet all the salespeople that are listed.
The match is what you are going to match from both sheets. Select the first Acct# that is listed on the posted memo sheet, and then select all of the customer numbers that are listed on the customer sheet. for further detail, visit : http://guides.library.upenn.edu/excel
- Press enter and then double click on the fill handle to populate the entire column
- Enter the sales people’s names in a separate column from the table.
- Use the sumif Function to total each of the individual sales
Select the column where the criteria exists, (sales persons) – these must be absolutely referenced – click the F4 key on each column reference.
Select the individual criteria, (Larry)
Select the column that you would like added (the Amount)– these must be absolutely referenced – click the F4 key on each column reference.
- Drag the formula down to complete all the sales for the Sales People
- Select the data in our newly created table and click the insert tab
- Select the Pie chart, from the Charts section
- Select the 2D Pie and your chart will be created
- Click on “Chart Title” and enter the desired text.
- You will now have a clear visualization of you data