Excel Formulas in Python: SUM, SUMIF, COUNTIF

If you already know Excel, you already know how to answer many common questions about data. You know how to use SUM, AVERAGE, COUNTIF, and probably SUMIFS when you need more than one condition.

Learning Python does not mean starting again from zero. In many cases, you are doing the same calculation with a different way of writing it.

In this tutorial, we will take a sales spreadsheet and answer ordinary questions you might normally solve with Excel formulas. We will start with totals and averages, move to SUMIF, COUNTIF, and SUMIFS, and finish by calculating revenue for every region at once.

We will ask one question at a time, inspect the generated Python, and check the result before moving on. This keeps a person in the analysis loop and makes it easier to connect familiar Excel ideas with pandas operations.

You can open the full MLJAR Studio conversation to inspect every prompt, generated Python cell, and output used in this tutorial.

Get the data

Download the demo files from datasets-for-start and save them in:

~/Documents/sales/
~/Documents/sales/

For this tutorial, we only need 2025-01.xlsx. It contains one month of sales data with 503 orders and seven columns.

Open the Excel file

Start MLJAR Studio and choose Start a New AI Conversation.

Click Read a File, choose a file from your computer, and select 2025-01.xlsx.

Reading is not uploading. MLJAR Studio opens 2025-01.xlsx directly from your disk. The generated Python reads the workbook and performs the calculations locally on your computer.

Studio loads the workbook and shows a preview.

The generated Python looks like this:

import pandas as pd

df = pd.read_excel("/home/piotr/Documents/sales/2025-01.xlsx")

print(f"Shape: {df.shape}")
columns = df.columns.tolist()
print(f"Columns ({len(columns)}):", columns if len(columns) <= 100 else columns[:100] + ["..."])
df.head()
import pandas as pd

df = pd.read_excel("/home/piotr/Documents/sales/2025-01.xlsx")

print(f"Shape: {df.shape}")
columns = df.columns.tolist()
print(f"Columns ({len(columns)}):", columns if len(columns) <= 100 else columns[:100] + ["..."])
df.head()

The result tells us:

Shape: (503, 7)
Columns (7): ['order_id', 'order_date', 'region', 'product', 'units', 'unit_price', 'revenue']
Shape: (503, 7)
Columns (7): ['order_id', 'order_date', 'region', 'product', 'units', 'unit_price', 'revenue']

We have 503 orders and seven columns.

In Excel, you might think of these as columns A through G and rows 2 through 504. In pandas, we usually refer to columns by their names rather than their letters.

Start with SUM, AVERAGE, and COUNT

Begin with three simple questions:

total revenue, average order value, and how many orders
total revenue, average order value, and how many orders

Studio generates:

summary = {
    "Total revenue (USD)": df["revenue"].sum(),
    "Average order value (USD)": df["revenue"].mean(),
    "Number of orders": df["order_id"].nunique(),
}

summary
summary = {
    "Total revenue (USD)": df["revenue"].sum(),
    "Average order value (USD)": df["revenue"].mean(),
    "Number of orders": df["order_id"].nunique(),
}

summary

The result is:

• Total revenue: 363,879.30 USD
• Average order value: 723.42 USD
• Orders: 503

The translation from Excel is direct:

The main difference is that Python uses the column name rather than its position. Instead of saying “column G,” we say revenue.

That is useful when a workbook changes. If someone inserts a column before revenue, G:G may no longer point to the values you expected. The name revenue does not move.

There is also a deliberate difference in the order count. nunique() counts distinct order IDs rather than every filled cell. Both approaches return 503 in this file. If duplicate order IDs existed, nunique() would not count the same identifier twice.

SUMIF and COUNTIF with one condition

Now ask how much revenue came from the North region and how many orders it had:

total revenue for the North region, and how many orders it had
total revenue for the North region, and how many orders it had

Studio generates:

north_summary = {
    "North total revenue (USD)": df.loc[
        df["region"].eq("North"), "revenue"
    ].sum(),
    "North orders": df.loc[
        df["region"].eq("North"), "order_id"
    ].nunique(),
}

north_summary
north_summary = {
    "North total revenue (USD)": df.loc[
        df["region"].eq("North"), "revenue"
    ].sum(),
    "North orders": df.loc[
        df["region"].eq("North"), "order_id"
    ].nunique(),
}

north_summary

The result is:

114,891.70 USD from 152 orders
114,891.70 USD from 152 orders

The Excel and Python versions express the same conditions:

The generated code uses nunique() for the order result because the question asks how many distinct orders the region had. A literal row-based COUNTIF equivalent is the Boolean sum shown in the table.

This condition:

df["region"].eq("North")
df["region"].eq("North")

selects the North rows. Then "revenue" selects the values to calculate, and .sum() adds them.

Read together, this expression means: take rows where region equals North, select revenue, and sum it.

df.loc[df["region"].eq("North"), "revenue"].sum()
df.loc[df["region"].eq("North"), "revenue"].sum()

Once the pattern makes sense, you can replace .sum() with .mean(), .min(), or .max() without learning another style of filtering.

SUMIFS with more than one condition

Now add a second condition. We want revenue from Keyboard orders, but only in the North region:

total revenue for Keyboard orders in the North region
total revenue for Keyboard orders in the North region

Studio generates:

keyboard_north = df.loc[
    df["region"].eq("North")
    & df["product"].str.contains("Keyboard", case=False, na=False)
]

keyboard_north["revenue"].sum()
keyboard_north = df.loc[
    df["region"].eq("North")
    & df["product"].str.contains("Keyboard", case=False, na=False)
]

keyboard_north["revenue"].sum()

The result is:

17,287.00 USD
17,287.00 USD

The Excel and Python versions share the same idea:

In pandas, & means “and.” Both conditions must be true for a row to be selected. You can add more conditions in the same way:

condition1 & condition2 & condition3
condition1 & condition2 & condition3

Excel has separate SUMIF and SUMIFS functions. In pandas, the aggregation remains .sum(); you add another condition to the filter.

The generated code uses:

.str.contains("Keyboard", case=False, na=False)
.str.contains("Keyboard", case=False, na=False)

That means the product name contains “Keyboard,” ignoring capitalization. It answers this dataset’s question, but a future product called Keyboard Stand would match too.

If the business question requires an exact product name, use:

df["product"].eq("Keyboard")
df["product"].eq("Keyboard")

This is why reviewing generated code matters. The result can be numerically valid while the matching rule is broader than you intended.

Calculate revenue for every region

So far, we have asked about one region at a time. Now calculate all regions:

total revenue for every region, sorted highest first
total revenue for every region, sorted highest first

Studio generates:

regional_revenue = (
    df.groupby("region", as_index=False)["revenue"]
    .sum()
    .sort_values("revenue", ascending=False)
    .rename(columns={"revenue": "total_revenue_usd"})
)

regional_revenue
regional_revenue = (
    df.groupby("region", as_index=False)["revenue"]
    .sum()
    .sort_values("revenue", ascending=False)
    .rename(columns={"revenue": "total_revenue_usd"})
)

regional_revenue

The result is:

groupby() divides the rows into groups and applies the same calculation to each one. Python creates groups for North, South, West, and East, then sums revenue inside every group.

The code does not need to know how many regions exist. If a fifth region appears next month, it appears automatically in the result.

You can group by more than one column too:

df.groupby(["region", "product"])["revenue"].sum()
df.groupby(["region", "product"])["revenue"].sum()

That returns revenue for every region-product combination, similar to a pivot table with multiple fields.

Before using the grouped result, check that its values add up:

114,891.70 + 97,687.40 + 83,745.90 + 67,554.30 = 363,879.30
114,891.70 + 97,687.40 + 83,745.90 + 67,554.30 = 363,879.30

The grouped totals match the overall revenue. This reconciliation confirms that no rows disappeared when we split the data by region.

Excel formulas and their pandas equivalents

Here is a compact reference for the operations used in this tutorial:

You do not need to memorize the table before using Python. The recurring pattern is more useful:

df["revenue"]
df["revenue"]

selects a column,

df["region"].eq("North")
df["region"].eq("North")

defines a condition, and

.sum()
.sum()

performs a calculation. Most examples on this page combine those three ideas.

Is Python faster than Excel for this?

For one simple question, Excel can be faster. If the workbook is already open and you only need the sum of one column, the status bar may answer immediately.

Python becomes more useful as the analysis grows or needs to be repeated. Several conditions require careful formula references in Excel. With the AI assistant, you can describe the question, inspect the generated filter, and preserve it in a notebook.

The difference is larger when you want all groups instead of one. Rather than writing several formulas, groupby() calculates every region in one operation. When next month’s workbook arrives, you can change the input file and rerun the analysis.

The advantage is not that Python is always quicker to type. It is that the steps are saved, reviewable, and repeatable. You can still use Excel for a quick look and Python for calculations or reports you want to run again.

Reuse the notebook

The conversation is stored as a normal Jupyter notebook. Prompts become markdown cells, generated Python becomes code cells, and results are saved beside them.

Next month, load 2025-02.xlsx instead of 2025-01.xlsx and run the notebook again. Total revenue, the North filter, the Keyboard calculation, and the grouped summary can all be recalculated on the new data.

You can extend the notebook with revenue by product, average order value by region, or units sold by product and region without rebuilding the workflow from scratch.

A note on privacy

MLJAR Studio works with files directly on your computer. The generated Python reads 2025-01.xlsx from your disk and performs the calculations locally, so you do not need to upload the workbook before Python can work with it.

There is a difference between where Python runs and where the AI model runs. With a cloud AI provider, the model may receive context such as your prompt, column names, dataframe shape, and preview values.

If you want the AI part to stay local too, MLJAR Studio can connect to local LLMs through Ollama, vLLM, LM Studio, Jan, and llama.cpp. In that setup, prompts, data context, generated code, and results do not need to be sent to a cloud AI provider.

What we did

We started with a January sales workbook containing 503 orders and translated familiar Excel calculations into pandas.

We used .sum() for SUM, .mean() for AVERAGE, a row filter for SUMIF and COUNTIF, two conditions for SUMIFS, and groupby() to calculate revenue for every region at once.

The goal was not to discard what you know about Excel. It was to carry those ideas into Python one reviewed step at a time. We inspected matching rules and reconciled the grouped result with the overall total before trusting the output.

Next steps

Continue with Merge All Excel Files in a Folder with Python to combine all twelve monthly workbooks before running similar calculations. If the source files need preparation first, follow Clean Messy Excel Data with Python and AI for a reviewable cleaning workflow.