Yes. In a sales log with quantity in column D and unit price in column E, enter =D2*E2 in F2 to calculate each line’s revenue, then use a formula such as SUMIFS to total sales by date or product. Keep one transaction per row, use consistent columns, and check that dates and amounts are stored as real values rather than text.
Choose columns that describe each sale
Start with a single worksheet in which every row represents one product line sold. If one order contains three different products, record three rows with the same order ID. This makes it possible to summarize products accurately; recording a whole multi-product order as one row makes product-level totals difficult.
A practical set of headers is:
- Date: the transaction or invoice date, used for time-based summaries.
- Order ID: a reference that identifies the order. It may appear on several rows if the order has multiple products.
- Product: a consistent product name or code.
- Quantity: the number of units on that line.
- Unit price: the selling price per unit.
- Line sales: the calculated amount for that product line.
- Salesperson or channel: optional fields if you need to compare representatives, stores, or online sales.
Put the headers in row 1 and enter transactions from row 2 onward. Avoid blank rows inside the list, merged cells, and multiple header rows: they can make formulas, sorting, and later analysis less dependable. Use one date format and one spelling or product code for each item.
Set up the log as an Excel table
After adding headers and a few records, select a cell in the range and choose Insert > Table in Excel for Microsoft 365 on Windows. Confirm that “My table has headers” is selected. A table expands when you add rows and generally carries formulas and formatting into new records, which is safer than maintaining a fixed range by hand.
Use the table’s filter arrows to find a date, product, or order. Sorting a table rearranges whole records, so the date, quantity, and price stay together. Do not sort just one column in isolation; that can detach values from the transactions they belong to. On Mac, the table feature is also available from the Insert tab, though the ribbon layout can differ. Excel for the web has table tools as well, with some interface differences.
For example, a small log might include a January 5 mug sale of two units at $12 each, a January 12 notebook sale of three at $8, a February 2 mug sale of one at $12, and a February 4 pen sale of five at $2. The resulting line sales are $24, $24, $12, and $10. This compact example can be used to check whether summary formulas are behaving as expected.
Calculate revenue for each transaction line
With quantity in D and unit price in E, enter =D2*E2 in F2. For the January 5 example row, D2 is 2 and E2 is 12, so the formula returns 24. Copy the formula down the column, or enter it in the first data row of a table and let Excel fill the calculated column.
Format the unit price and line sales columns as currency using Home > Number, choosing a currency format appropriate to your records. Formatting changes how values look; it does not convert text into numbers. If multiplication returns an error or a zero where an amount is expected, check that quantity and price are numeric values, not text copied from another system.
This calculation is revenue before any adjustments not represented in the columns. If the business applies discounts, returns, or shipping charges, define how those affect the figure before calling it “sales.” For example, a discount can be represented with an additional discount column and a net line calculation, while a return can be entered as a negative adjustment according to the organization’s accounting process. Tax collected, cash received, and recognized revenue are not automatically the same measure; a simple sales log does not settle accounting or tax treatment.
Total sales for a month or date range
SUMIFS adds values only when the specified criteria match. For a monthly report, put the first date of the period in H2 and its last date in H3. With transaction dates in A2:A1000 and line sales in F2:F1000, enter this formula in H4:
Assuming A2:A1000 contains transaction dates, F2:F1000 contains line sales, H2 is January 1, 2026, and H3 is January 31, 2026, enter =SUMIFS($F$2:$F$1000,$A$2:$A$1000,">="&H2,$A$2:$A$1000,"<"&H3+1) in H4. The illustrative four transactions above return $48 for January: $24 plus $24. February’s two lines total $22.
The formula uses an inclusive start and an exclusive date after the end date. That boundary is useful if the date column contains times as well as dates, because a transaction recorded late on January 31 still falls before February 1. The ampersand joins each comparison operator to the date in the criteria cell. The dollar signs keep the source ranges fixed if you copy the formula to another report cell.
Keep the start and end cells as real Excel dates. If they are text, criteria may not match as intended. In some regional settings, Excel expects semicolons instead of commas between function arguments; the formulas here use the comma separators shown in English-language Excel. If your installation uses another separator, use the one Excel expects.
Summarize sales by product
A product summary can use the same SUMIFS function. List each product to report in column J and put its total beside it. With product names in C2:C1000, line sales in F2:F1000, and the product to summarize in J2, enter =SUMIFS($F$2:$F$1000,$C$2:$C$1000,J2) in K2. If J2 contains Mug, the example data returns $36, from $24 and $12.
Copy the formula down for other product names. Because J2 is a relative reference, it changes to J3, J4, and so on, while the dollar signs hold the transaction ranges in place. The result depends on names matching exactly: “Mug” and “Mugs” are separate labels. For that reason, use a consistent product list, product codes, or a drop-down list rather than typing names differently on each sale.
SUMIFS is useful for other comparisons too. If you add a salesperson column, you can summarize by representative; if you add a sales-channel column, you can compare channels. The key is to keep one transaction attribute per column and make sure the criteria range has the same number of rows as the sum range.
Make data entry and review easier
A tracker works only as well as the records entered into it. A few simple controls reduce avoidable mistakes:
- Use Data > Data Validation to restrict entries where appropriate, such as whole-number quantities or a list of approved product names.
- Use a consistent date convention and enter actual dates, not labels such as “Jan sale.”
- Use filters to inspect missing product names, unusual quantities, or a particular period.
- Apply conditional formatting sparingly to draw attention to blanks or unusually large values; verify the underlying records rather than assuming a highlight proves an error.
- Keep a source document or order reference so a questionable row can be checked against the original transaction.
You can also create a chart from a summarized report rather than charting every transaction row. Select the summary labels and totals, then choose Insert > Recommended Charts or a chart type from the Insert tab. A line chart can show totals over time, while a bar or column chart can compare products. Label the period and currency clearly, and avoid mixing daily and monthly figures in the same series.
Check common formula and reporting problems
A total that seems too high or too low often reflects a data issue rather than a broken function. Check these points before rebuilding the workbook:
- Incomplete ranges: A formula that ends at row 100 may omit later records. Tables or deliberately ample ranges help prevent this.
- Dates stored as text: A date can look correct but fail a date criterion. Test a few cells by changing their number format or re-entering them as dates.
- Duplicate records: SUMIFS adds every matching row, including duplicates. Compare order IDs with source records when a total looks unexpectedly high.
- Inconsistent categories: Extra spaces, spelling differences, or different product codes split a product’s total across labels.
- Wrong metric: Line sales may be gross of discounts or exclude returns, shipping, and tax. State what the report includes.
Do not confuse the number of rows with the number of orders. If an order has several product lines, summing line sales is appropriate for revenue, but counting rows does not give the count of distinct orders. Similarly, a workbook that records sales is not necessarily a live connection to a point-of-sale system; unless data is imported or entered, formulas cannot know about transactions that are missing from the sheet.
Formulas or a PivotTable?
Formulas are a good fit when the report has a small set of stable totals, such as monthly sales and sales by product, and you want the calculation visible in cells. They also work well when another worksheet or document needs to link to particular summary cells. Their main maintenance risk is that a copied formula can point to an incorrect range or criteria cell.
A PivotTable is often more convenient when you want to explore many combinations of dates, products, salespeople, and channels without writing a new formula for each view. Select a cell in the data table and use Insert > PivotTable, then arrange fields in the Rows, Columns, Values, and Filters areas. Refresh the PivotTable after adding or changing source records. A table-based source generally makes it easier for the report to include newly added rows.
These approaches can coexist: formulas can provide a fixed monthly summary, while a PivotTable supports ad hoc analysis. Neither method corrects bad source data or decides which accounting measure is appropriate. Choose based on how often the report changes and who needs to maintain it.
Frequently Asked Questions
What is the simplest formula to calculate sales in Excel?
For a line with quantity in D2 and unit price in E2, use =D2*E2. It returns the line amount before any discounts, returns, tax, or other adjustments not included in the calculation.
How do I total sales between two dates?
Use SUMIFS with the sales amount range and date range, applying a start-date criterion and an end boundary. Keep the start and end dates in cells so you can change the report period without editing the formula.
Can Excel calculate sales by product?
Yes. Use SUMIFS with the product column as a criteria range and a cell containing the product name as the criterion. Consistent product names or codes are essential for a complete total.
Why does my sales total not include new rows?
The formula may use a fixed range that stops above the added transactions. Convert the source list to an Excel table, or update the formula’s ranges so they include the new rows.
Can I use this tracker for profit?
Not from sales price and quantity alone. Profit calculations require relevant costs and a clear definition of which costs are included. A sales total should not be presented as profit unless those costs and assumptions are accounted for.
Related
- Simple Project Timeline in Excel: Build a Clear Schedule
- Simple Checklist Template Excel: Build a Useful One
- Simple Inventory Tracking in Excel: A Practical Setup
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Text Join in Excel: Formulas and Practical ExamplesIn Excel for Microsoft 365, use TEXTJOIN to combine text from cells with a separator such as a…
- Vending Machine Inventory Spreadsheet: Setup and FormulasA vending machine inventory spreadsheet tracks what is loaded, what remains, what sold, and what needs restocking. Start…
- Convert Dates to Text in Excel: Formulas and MethodsIf the dates are real Excel dates in A2:A1000, enter =TEXT(A2,"mmmm d, yyyy") in B2 and fill it…
- How to Filter Within a Filter in ExcelTo filter within an existing Excel filter, leave the first filter in place and apply another filter from…
- Excel IFS Function: Syntax, Examples, and Common ErrorsExcel’s IFS function contains pairs of logical tests and the values to return when those tests are true.…
- Excel Mortgage Formula PMT: How to Use ItThe Excel PMT function calculates the periodic payment for a loan based on a constant interest rate and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.