Retainer Hours Used and Remaining

Excel Formulas › Freelance & Agency

All versionsSUMIF

A retainer buys a block of hours each period. Track hours used against the allotment, and flag overage — SUMIF totals logged hours, and a subtraction shows what’s left.

Quick formula: hours remaining on a retainer:
=retainer_hours - SUMIF(month, this_month, hours_logged)
Allotted hours minus the sum of hours logged this month; negative means overage.

Functions used (tap for the full reference guide):

SUMIFMAX

The example

20-hour retainer, 14 logged.

AB
1ItemValue
2Retainer20
3Used 14→ 6 left

The formula

The formula:

=retainer_hours - SUMIF(month, this_month, hours) // allotment − hours used

How it works

How it works:

  1. SUMIF(month, this_month, hours) totals the hours logged in the current period.
  2. Subtract from the retainer allotment for hours remaining.
  3. A negative result is overage — bill it at the overage rate or roll it forward.
  4. Wrap remaining in MAX(…, 0) and overage in MAX(used - allotment, 0) to split cleanly.

Split remaining and overage explicitly. =MAX(allotment - used, 0) gives hours left (never negative) and =MAX(used - allotment, 0) gives billable overage. Two clean cells beat one signed number — clients see exactly what’s included and what’s extra, and the overage cell feeds straight into an invoice line.

Try it: interactive demo

Live demo

Retainer hours and hours used.

Remaining · Overage

Variations

Hours remaining (floored)

Never negative:

=MAX(retainer_hours - used, 0)

Overage hours

Billable extra:

=MAX(used - retainer_hours, 0)

Overage charge

At overage rate:

=MAX(used - retainer_hours, 0) * overage_rate

Pitfalls & errors

Reset each period. Filter hours to the current month with SUMIF, or last period bleeds in.

Define rollover. Decide whether unused hours carry forward before flooring at 0.

Overage rate. Overage often bills higher than the retainer rate — use the right one.

Practice workbook

📊
Download the free Retainer Hours Used and Remaining practice workbook
A retainer sheet with the floored-remaining, overage, and overage-charge variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I track retainer hours in Excel?
Subtract logged hours from the allotment: =retainer_hours - SUMIF(month, this_month, hours). Negative means overage.
How do I separate remaining hours from overage?
Use =MAX(allotment - used, 0) for remaining and =MAX(used - allotment, 0) for overage.
How do I bill overage?
Multiply overage hours by the overage rate: =MAX(used - allotment, 0) * overage_rate.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: SUMIF if contains · Running total · Effective hourly rate

Function references: SUMIFMAX