Excel Tips

Tips from the Finance Trenches: How to Summarize Revenue by Region and Sales Lead

Excel is a powerful tool that allows users to quickly manipulate and analyze key data – yet it’s not unusual for finance and accounting teams to get hung up on practical functions in creating reports and summaries. In this quick tip, we will walk you through five easy steps (with illustrations) to help you summarize sales data. While this functionality can be applied to different scenarios, our goal in this example  is to arrive at a report that summarizes Revenue by Sales Lead and Region. To start, here’s a look at a list of sales leads, each associated with region and revenue for 2016.

Step 1. First, we’ll need to identify the unique regions. To do this, copy and paste the Region column (from above) into a new tab; then highlight the Region column from that newly created tab.

Step 2. Still in your new tab, click on the Data tab in your top toolbar, as shown below. Then select Remove Duplicates.

arrow

Step 3:  Now that your Regions are collapsed into distinct categories, you can use the sumif function to calculate revenue by region for your desired summary table. Start by entering =sumif(   into the top formula bar, as shown below. The required syntax will pop up (Range, Criteria, Sum Range).

To select the values for that required criteria, follow the steps that correlate to the diagram above:

  1. In the formula cells, select your “Range.” This will include the Region column from the original data set.
  2. Next select the “Criteria.” This will include the Region row from your newly created summary table.
  3. Finally, select the “Sum Range.” This will include the Revenue column in the original data set. 

Step 4:  Once you’ve selected the criteria required by the formula in Step 3, simply close your parenthesis to complete the formula. The formula will now summarize your revenue by your distinct regions.

Step 5. Repeat Steps 3 and 4 to create formulas for your remaining regions – then you are done!

I hope you find this everyday tip useful. If you’re Interested in more Tips from the Accounting Trenches, be sure to check out the following:

Keep in mind, 8020 Consulting can help you leverage more advanced capabilities in Excel that can make an immediate impact on your organization. Do you have questions about Accounting or other complex financial matters? Just click the button below to connect with an expert. 

Categorized in:

similar articles

Learn to think and approach problems like our financial consultants.

Manufacturing Operations Finance

Time to Clean Out the Warehouse: Improving Inventory Management Processes in 2021

Inventory flows reveal a lot about the health of a company, and 2020 put many stresses on inventory management processes. There were demand shocks, rising transportation costs, supply chain shortages, labor constraints and many other impacting factors. Manufacturers and distributors were suddenly faced with a slew of underperforming SKUs while others were in short supply…. View Article

January 20, 2021Danelle Larsen

Financial Project Management

Notes from the Field: 5 Success Factors for Cross-Functional Project Management

Finance teams are often looked to for leadership of cross-functional projects. This is often the case because the ultimate project results can be measured by, or affect, financials. A budget or re-forecast process is a clear example of a cross-functional project led by Finance. From our experience in leading cross-functional projects as part of Finance… View Article

January 13, 2021Marco Moreno

Treasury and Cash Management

A 2020 Recap: Cash Management & Why Cash is Still King

Last year brought disruptions in almost every industry. We witnessed significant interruptions in order fulfillment and delivery systems, chaotic drops and soaring demand in certain categories, fresh liquidity for borrowers and cash crunches for others. And cash management underwent rapid change for those suddenly with too much or too little. Short-Term Cash Management Led to Underutilized… View Article

January 7, 2021Danelle Larsen

See All