ThisCalculator

Everyday

Subtotal by Invoice Number: How It Works

Updated · Standard formula, shown below

The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.

Subtotal by invoice number means grouping and summing line items that share the same invoice identifier, so you can see the total for each invoice separately. You enter a list of invoice numbers and their amounts, and the calculator below returns the subtotal for each invoice, plus the overall total.

Formula

The subtotal for a given invoice number is the sum of all amounts that have that invoice number. In mathematical terms:

Subtotali = Σ Amountk for all rows k where Invoicek = Invoicei

Where:

  • Invoicei is the invoice number you are subtotaling.
  • Amountk is the amount in row k.
  • Σ means “sum of.”

In practice, you often use a spreadsheet function like SUMIF or a database GROUP BY clause to do this automatically. The calculator above demonstrates the concept with a simple example.

Worked Example

Using the default values from the calculator:

  • Invoice numbers: 1001, 1002, 1001, 1003, 1002
  • Amounts: 250.75, 120.00, 89.50, 300.00, 75.25

To find the subtotal for invoice 1001:

  1. Identify rows with invoice 1001: row 1 (250.75) and row 3 (89.50).
  2. Add them: 250.75 + 89.50 = 340.25.

So the subtotal for invoice 1001 is 340.25. The overall total of all amounts is 250.75 + 120.00 + 89.50 + 300.00 + 75.25 = 835.50.

Why Subtotal by Invoice Number Matters

When you have a list of transactions—sales, purchases, expenses—each line typically belongs to an invoice. Summing by invoice number gives you the total for each invoice, which is essential for:

  • Checking that an invoice matches the sum of its line items.
  • Reconciling payments received against invoices.
  • Producing reports that show totals per customer or per order.
  • Identifying duplicate or missing invoices.

Without subtotaling, you only see the grand total, which hides how much each invoice contributes.

How to Subtotal in Spreadsheets

Most people subtotal by invoice number using a spreadsheet. Here are common methods:

  • SUMIF: In Excel or Google Sheets, use =SUMIF(range, criteria, sum_range). For example, =SUMIF(A:A, 1001, B:B) sums all amounts in column B where column A equals 1001.
  • Pivot Table: Select your data, insert a pivot table, drag “Invoice Number” to Rows and “Amount” to Values. The pivot table automatically shows subtotals per invoice.
  • Subtotal Tool: Excel’s Data > Subtotal feature groups data by a column and inserts subtotal rows.
  • Database Query: In SQL, use SELECT invoice_number, SUM(amount) FROM table GROUP BY invoice_number;

Common Mistakes and How to Avoid Them

Subtotaling seems simple, but errors happen. Watch out for:

  • Mismatched data types: If invoice numbers are stored as text in one place and numbers in another, they won’t match. Ensure consistent formatting.
  • Hidden rows or filters: In spreadsheets, SUMIF ignores hidden rows only if you use SUBTOTAL, not SUMIF. Be aware of what your function includes.
  • Blank or duplicate invoice numbers: Blank invoice numbers may be grouped together, and duplicates may indicate data entry errors.
  • Rounding: If you round each line item to two decimals, the subtotal may differ from the sum of unrounded amounts by a few cents. Use consistent rounding.

When to Use Subtotal vs. Total

A total is the sum of all amounts in your dataset. A subtotal is the sum within a group. For example, if you have 10 invoices, the total is the sum of all 10, while each invoice’s subtotal is the sum of its own line items.

Use subtotals when you need to:

  • Verify each invoice’s correctness.
  • Compare invoices to payments.
  • Analyze sales per customer or per period.

Use the overall total when you need a single figure for the whole dataset, such as monthly revenue.

Limitations of the Calculator

This calculator demonstrates the concept with a small list. For real-world data, you’ll likely use a spreadsheet or database. The calculator does not handle:

  • Large datasets (thousands of rows).
  • Multiple criteria (e.g., subtotal by invoice AND product).
  • Date ranges or other filters.

For those, use a pivot table or SQL query.

Frequently Asked Questions

How do I subtotal by invoice number in Excel?

Use the SUMIF function: =SUMIF(A:A, 1001, B:B) where column A has invoice numbers and column B has amounts. Replace 1001 with a cell reference to make it dynamic.

Can I subtotal by invoice number in Google Sheets?

Yes, Google Sheets supports SUMIF and also has a QUERY function: =QUERY(A:B, "select A, sum(B) where A is not null group by A").

What is the difference between subtotal and total?

Total is the sum of all values. Subtotal is the sum of a subset of values that share a common attribute, like an invoice number.

Why do my subtotals not match the total?

Check for hidden rows, filters, or data type mismatches. Also, if you have duplicate invoice numbers, subtotals will include both, which is correct but may confuse you if you expected unique invoices.

How do I subtotal by invoice number in SQL?

Use a GROUP BY clause: SELECT invoice_number, SUM(amount) FROM transactions GROUP BY invoice_number;

Can I subtotal by invoice number without a formula?

Yes, use a pivot table in Excel or Google Sheets. Drag the invoice number field to Rows and the amount field to Values—the pivot table automatically shows subtotals.

Related

Formula used
in t | Invoice number | - | 1001 | 0
in a | Amount | - | 250.75 | 0
list inv | Invoice numbers | 1001, 1002, 1001, 1003, 1002
list amt | Amounts | 250.75, 120.00, 89.50, 300.00, 75.25
out subtotal | Subtotal per invoice | - | sum(if(inv=1001, amt, 0)) | 2
out total | Overall total | - | sum(amt) | 2

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.