Image by Editor | Midjourney
When working with Excel files in Python, you often need to both count occurrences and calculate sums across different categories. While Excel provides built-in functions like COUNTIF() and SUMIF(), automating these operations with Python allows you to process multiple files consistently and build more sophisticated analysis pipelines. In this article, we’ll show how to combine these techniques using openpyxl to analyze sales performance data.
We’ll create a sales analysis workbook that:
- Counts the number of deals per sales representative
- Calculates total sales by region
- Applies conditional formatting to highlight high-performing regions
Let’s get started by creating a sample dataset.
Setting Up the Initial Data Structure
Our sample dataset will consist of sales representatives, their regions, and individual sale amounts. We’ll use openpyxl to create a new workbook and populate it with our data.
from openpyxl import Workbook
from openpyxl.styles import Font
from openpyxl.utils import get_column_letter
# Create a new workbook and select active sheet
wb = Workbook()
ws = wb.active
ws.title = "Sales Data"
# Sample data: Sales Rep, Region, Sales Amount
data = [
("John", "North", 50000),
("Sarah", "South", 75000),
("John", "North", 62000),
("Mike", "East", 45000),
("Sarah", "South", 68000),
("Lisa", "West", 55000),
("John", "North", 58000),
("Mike", "East", 51000),
("Lisa", "West", 59000),
]
# Add headers with formatting
headers = ["Sales Rep", "Region", "Sales Amount"]
for col, header in enumerate(headers, 1):
cell = ws.cell(row=1, column=col, value=header)
cell.font = Font(bold=True)
# Add data
for row, (rep, region, amount) in enumerate(data, 2):
ws.cell(row=row, column=1, value=rep)
ws.cell(row=row, column=2, value=region)
ws.cell(row=row, column=3, value=amount)
# Adjust column widths
for col in ws.columns:
max_length = max(len(str(cell.value)) for cell in col)
ws.column_dimensions[col[0].column_letter].width = max_length + 2
# Save the initial workbook
wb.save('sales_analysis_step1.xlsx')
This initial code block sets up our foundation by:
- Creating a new Excel workbook with a “Sales Data” sheet
- Adding sample sales data with three columns: Sales Rep, Region, and Sales Amount
- Formatting headers in bold
- Automatically adjusting column widths for better readability
When you open the generated Excel file, you’ll see:
Image By Author
The data is now organized in a clean, readable format that we can build upon for our analysis. In the next section, we’ll add functionality to count deals per sales representative.
Adding Deal Count Analysis for Sales Representatives
With our basic data structure in place, let’s add functionality to count how many deals each sales representative has closed. We’ll create two new columns that show each unique sales rep and their total deal count. This gives us a quick view of each rep’s activity level.
from openpyxl import load_workbook
from openpyxl.styles import Font
# Load the workbook from step 1
wb = load_workbook('sales_analysis_step1.xlsx')
ws = wb.active
# Add headers for count analysis (Column E and F)
ws.cell(row=1, column=5, value="Unique Reps").font = Font(bold=True)
ws.cell(row=1, column=6, value="Deal Count").font = Font(bold=True)
# Get unique sales reps
reps = set(ws.cell(row=r, column=1).value for r in range(2, ws.max_row + 1))
# Count deals for each rep
for idx, rep in enumerate(sorted(reps), 2):
# Add rep name
ws.cell(row=idx, column=5, value=rep)
# Count deals
count = sum(1 for r in range(2, ws.max_row + 1)
if ws.cell(row=r, column=1).value == rep)
ws.cell(row=idx, column=6, value=count)
# Adjust new column widths
for col in [5, 6]: # New columns
max_length = max(len(str(ws.cell(row=r, column=col).value)) for r in range(1, ws.max_row + 1))
ws.column_dimensions[get_column_letter(col)].width = max_length + 2
# Save the workbook with count analysis
wb.save('sales_analysis_step2.xlsx')
This code builds on our initial structure by:
- Loading the existing workbook we created in step 1
- Adding two new columns: “Unique Reps” and “Deal Count”
- Using set() to identify unique sales representatives
- Counting deals for each rep using a list comprehension with sum()
- Maintaining consistent formatting with bold headers and adjusted column widths
Here’s how the Excel file looks after running this code:
Image By Author
Looking at the results, we can see that John has closed 3 deals, while Sarah, Mike, and Lisa have each closed 2 deals. This kind of analysis helps sales managers track team performance and workload distribution.
In the next section, we’ll add regional sales analysis to identify our highest-performing territories.
Creating Regional Sales Analysis with Conditional Formatting
The final step is to analyze total sales by region and highlight high-performing territories. We’ll add two new columns that calculate regional totals and use conditional formatting to visually identify regions exceeding $100,000 in sales.
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill
from openpyxl.utils import get_column_letter
# Load the workbook from step 2
wb = load_workbook('sales_analysis_step2.xlsx')
ws = wb.active
# Add headers for sum analysis (Column H and I)
ws.cell(row=1, column=8, value="Region").font = Font(bold=True)
ws.cell(row=1, column=9, value="Total Sales").font = Font(bold=True)
# Get unique regions
regions = set(ws.cell(row=r, column=2).value for r in range(2, ws.max_row + 1))
# Sum sales for each region
for idx, region in enumerate(sorted(regions), 2):
# Add region name
ws.cell(row=idx, column=8, value=region)
# Calculate total sales
total = sum(ws.cell(row=r, column=3).value
for r in range(2, ws.max_row + 1)
if ws.cell(row=r, column=2).value == region)
cell = ws.cell(row=idx, column=9, value=total)
# Add conditional formatting for high-performing regions
if total > 100000:
cell.fill = PatternFill(
start_color="90EE90", # Light green
end_color="90EE90",
fill_type="solid"
)
# Adjust new column widths
for col in [8, 9]: # New columns
max_length = max(len(str(ws.cell(row=r, column=col).value)) for r in range(1, ws.max_row + 1))
ws.column_dimensions[get_column_letter(col)].width = max_length + 2
# Save the final workbook
wb.save('sales_analysis_final.xlsx')
This final code section:
- Adds “Region” and “Total Sales” columns
- Uses set() to identify unique regions
- Calculates total sales for each region using list comprehension with sum()
- Applies green highlighting to regions with sales over $100,000
- Maintains consistent formatting with adjusted column widths
Here’s the final Excel file with all our analysis:
Image By Author
The results show that:
- North region leads with $170,000 in sales
- South follows with $143,000
- West achieved $114,000
- East totaled $96,000
Three out of four regions exceeded our $100,000 threshold, indicated by the green highlighting.
This combined analysis gives sales managers a complete view of both individual performance and regional success. By automating these calculations with Python and openpyxl, you can easily apply this analysis to larger datasets or multiple sales reports.
Conclusion
This guide demonstrates how to combine Excel’s counting and summing functions using Python and openpyxl by creating organized sales data structures, counting deals per representative automatically, and calculating regional performance with conditional formatting. The techniques shown here scale well for larger datasets, letting you process multiple Excel files at once, add sophisticated conditional formatting rules, include additional analysis metrics, and export results to new workbooks.
For additional Excel automation techniques using Python and openpyxl, check out our related guides on:
