Subtotal Invoices by Age: How to Do It

This article explains how to subtotal invoices by age—grouping outstanding invoices into buckets like current, 30, 60, and 90+ days past due. You’ll learn the formulas and steps for Excel, Google Sheets, and popular accounting tools, plus how to interpret the results for cash flow and collections.

Formula

The core formula for aging subtotals is a conditional sum. For each invoice, you check if the days past due falls within a bucket’s range. The formula in Excel is:

=SUMIFS(amount_range, days_past_due_range, ">="&start, days_past_due_range, "<"&end)

Where:

• amount_range – the column with invoice amounts (currency).
• days_past_due_range – the column with days past due (number).
• start – the lower bound of the bucket (e.g., 0 for current).
• end – the upper bound (e.g., 30 for 1-30 days).

For example, to sum invoices that are 31 to 60 days past due:

=SUMIFS(C2:C100, D2:D100, ">=31", D2:D100, "<=60")

The calculator above lets you test a single invoice’s bucket membership: enter the days past due and the bucket range, and it returns the amount if it falls in that bucket, otherwise 0.

Worked Example

Using the default values: an invoice of $1,000 is 30 days past due, and the bucket is 0–30 days. Since 30 is included (using >= and <=), the amount is $1,000.

If you change the bucket to 31–60, the same invoice would return $0. This demonstrates the importance of defining your bucket boundaries clearly.

When to Use Aging Subtotals

Aging reports are standard in accounts receivable. They help you:

• Identify overdue invoices that need collection follow-up.
• Estimate cash flow by predicting when payments might arrive.
• Assess the risk of bad debts—older invoices are less likely to be paid.
• Provide data for financial statements and audits.

Most businesses run aging reports monthly, but you can do it weekly if you have many invoices.

How to Subtotal by Age in Excel

1. Prepare your data: columns for invoice number, date, amount, and days past due. Calculate days past due with =TODAY()-date.
2. Create a summary table with bucket labels (Current, 1-30, 31-60, 61-90, 90+).
3. Use SUMIFS for each bucket, referencing the amount and days past due columns.
4. Format the numbers as currency and add totals.

For example, if your data is in rows 2 to 100, the formula for the 31-60 bucket would be:

=SUMIFS($C$2:$C$100, $D$2:$D$100, ">=31", $D$2:$D$100, "<=60")

Copy this down for each bucket, adjusting the thresholds.

How to Subtotal by Age in Google Sheets

Google Sheets uses the same SUMIFS syntax. The only difference is that you might use ARRAYFORMULA for dynamic ranges, but the basic approach is identical.

1. Enter your invoice data with a days past due column.
2. Create a summary area with bucket labels.
3. Use SUMIFS with the same criteria.
4. Alternatively, use a pivot table: drag “Days Past Due� to Rows, group by 30-day intervals, and drag “Amount� to Values.

Pivot tables are faster for large datasets and automatically update when you refresh.

Common Mistakes and How to Avoid Them

• Inconsistent bucket boundaries – Decide whether a day is included in one bucket or the next. Use >= and < consistently.
• Using text dates instead of real dates – Excel can’t calculate days past due from text. Ensure dates are in date format.
• Forgetting to update the “todayâ€� date – If you use TODAY(), it recalculates each day. If you hardcode a date, it becomes stale.
• Mixing currencies or ignoring credit memos – Make sure all amounts are in the same currency and include adjustments.
• Not handling negative days – Invoices paid early have negative days past due. Decide whether to include them in “currentâ€� or exclude them.

For example, if an invoice is paid on day 29, it falls into the 1-30 bucket. If you use <30 instead of <=30, it would be missed.

Comparing Methods: Manual vs. Software

Manual spreadsheets give you full control and are free, but they require regular updating and are prone to human error. Accounting software like QuickBooks, Xero, or FreshBooks automatically generate aging reports from your transactions, saving time and reducing mistakes. However, they may have fixed bucket definitions (e.g., 1-30, 31-60, 61-90) that you can’t customize without workarounds.

If you use software, you can still export the data to Excel for custom subtotals. The choice depends on your volume, budget, and need for customization.

Frequently Asked Questions

How do I calculate days past due in Excel?

Use =TODAY()-invoice_date. Format the cell as a number. If the result is negative, the invoice is not yet due.

What is the standard aging bucket?

Most companies use: Current (0-30), 1-30 days past due, 31-60, 61-90, and 90+. Some add a “not yet due� bucket for future dates.

Can I subtotal by age in Google Sheets without formulas?

Yes, use a pivot table. Group the days past due field by 30-day intervals and summarize the amount.

Why does my aging report show negative days?

Negative days mean the invoice is not yet due. You can filter them out or include them in a “future� bucket.

Should I include paid invoices in the aging report?

No, aging reports typically show only unpaid invoices. Paid invoices are removed once payment is recorded.

Related

• Windows Storage Sense vs Duplicate File Finders: What Each Cleans
• Better Online Co-op Communication & Coordination Guide
• Why Steam Goes Down & How It Affects Your Games

in t | Days past due | days | 30 | 0
in d | Invoice amount | $ | 1000 | 0
in a | Aging bucket start | days | 0 | 0
in b | Aging bucket end | days | 30 | 0
out bucket | Amount in bucket | $ | if(t>=a and t