Excel formula: Subtotal invoices by age

Generic formula

=SUMIF(age,criteria,amount)

Summary

If you need to subtotal invoice amounts by age, you can easily do so with the SUMIF function.

In the example shown, the formula in G5 is:

=SUMIF(age,G5,amount)

Where age is a named range for the range E5:E11, and amount is a named range for D5:D11.

Explanation

The SUMIF function takes three arguments: range, criteria, and sum_range.

In this case, we are using:

Range: the range of cells (E5:E11) to which criteria in column G is applied.

Criteria: G5 - a reference to the adjacent cell in column G, which contains the criteria. This is a relative address so that it will change when copied down.

Sum_range: the range of cells (D5:D11) that is summed when the criteria is TRUE.

Author

Dave Bruns

Related formulas

Sum if multiple criteria

The first range (D4:D11) are the cells to sum, called the "sum range". Criteria are supplied in pairs...(range / criteria). The first criteria pair is (B4:B11 / "blue"). This means cells in B4:B11 must equal "blue". The second criteria pair is (C4:...

Subtotal by color

The SUMIF function takes three arguments: range, criteria, and sum_range. In this case, we are using: Range: $B$4:$B$11 - This is the set of cells to which the criteria (a color from column F in this case) will be applied. This is an absolute...

Related functions

Excel SUMIF Function

The Excel SUMIF function returns the sum of cells that meet a single condition. Criteria can be applied to dates, numbers, and text. The SUMIF function supports logical operators (>,,=) and wildcards (*,?) for partial matching....

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.