Bill Hours at Different Rates

Excel Formulas › Business

All versionsSUMPRODUCT

Consultants and agencies bill different tasks at different rates. SUMPRODUCT multiplies each task’s hours by its rate and totals the bill in one cell — and SUMIF rolls hours up by person or role.

Quick formula: for hours in B2:B10 and rates in C2:C10:
=SUMPRODUCT(B2:B10, C2:C10)
Each task’s hours × its rate, summed — the total billable amount across the whole project.

Functions used (tap for the full reference guide):

SUMPRODUCTSUMIF

The example

Three tasks at different hourly rates.

ABC
1TaskHoursRate
2Design10$120
3Dev20$150
4QA8$90
5Total bill$4,920

The formula

Total the billable amount:

=SUMPRODUCT(B2:B4, C2:C4) // 10×120 + 20×150 + 8×90 = 4,920

How it works

SUMPRODUCT pairs hours with rates automatically:

  1. It multiplies each row’s hours by its rate, then sums the products — the whole bill in one formula.
  2. No per-row “amount” column required, though you can add one (=B2*C2) for the printed invoice.
  3. To total hours (not dollars) for one person, use SUMIF(names, "Ana", hours).
  4. To total dollars for one person, add a criteria term: =SUMPRODUCT((names="Ana")*hours*rates).

One blended rate? If everyone bills the same, skip SUMPRODUCT and use =SUM(hours)*rate. SUMPRODUCT earns its keep precisely when rates vary by task, person, or seniority.

Try it: interactive demo

Live demo

Lines as “hours,rate”.

Hours · Total

Variations

Hours for one person

Roll up by name:

=SUMIF(names, "Ana", hours)

Dollars for one person

Criteria inside SUMPRODUCT:

=SUMPRODUCT((names="Ana")*hours*rates)

Add a markup

Bill above cost:

=SUMPRODUCT(hours, rates) * (1 + markup)

Pitfalls & errors

Equal-length ranges. Hours and rates must span the same rows or SUMPRODUCT returns #VALUE!.

Watch time formats. If hours are stored as time (8:30) rather than a number (8.5), multiply by 24 first, or the math is off by a factor of 24.

Blank rate = free work. A missing rate multiplies to 0. Validate that every billed task has a rate.

Practice workbook

📊
Download the free Bill Hours at Different Rates practice workbook
A billing sheet with SUMPRODUCT totals, the by-person hours/dollars and markup variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I bill hours at different rates in Excel?
Use =SUMPRODUCT(hours_range, rates_range). It multiplies each task's hours by its rate and sums the result in one cell.
How do I total billing for one person?
For dollars use =SUMPRODUCT((names="Ana")*hours*rates); for just hours use =SUMIF(names,"Ana",hours).
My total is 24× too big — why?
Your hours are stored as time values (like 8:30), not numbers. Multiply by 24 to convert to decimal hours before applying the 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: SUMPRODUCT formula · Timesheet overtime · Invoice total with tax

Function references: SUMPRODUCT · SUMIF