Subtotal invoices by age

To subtotal invoice amounts by age, you can use the SUMIFS function and the COUNTIFS function. In the example shown, the formula in I5 is:

=SUMIFS(amount,age,"<=30")

Where age (E5:E16) and amount (D5:D16) are named ranges. See below for the formulas in I6 and I7, and the formulas in H5:H7.

Explanation

In this example, the goal is to subtotal invoices by age, where age represents the number of days since the invoice was issued. This problem can be solved with the SUMIFS function and the COUNTIFS function, as explained below. For convenience, age (E5:E16) and amount (D5:D16) are named ranges.

SUMIFS function

The SUMIFS function is designed to sum cells that meet multiple criteria. SUMIFS takes at least three arguments like this:

=SUMIFS(sum_range,range1,criteria1)

Notice sum_range appears first. Additional criteria are added in range/criteria pairs like this:

=SUMIFS(sum_range,range1,criteria1,range2,criteria2)

The formulas used to sum invoices by age in I5:I7 are as follows:

SUMIFS(amount,age,"<=30")
SUMIFS(amount,age,">30",age,"<=45")
SUMIFS(amount,age,">45")

Notice that criteria appear in double quotes (""). SUMIFS is in a group of eight functions that share this syntax.

COUNTIFS function

To count invoices by age, you can use the COUNTIFS function, which is designed to count cells based on multiple criteria. Like SUMIFS, COUNTIFS accepts arguments as range/criteria pairs:

=COUNTIFS(range1,criteria1,range2,criteria2)

The formulas used to count invoices by age in H5:H7 are as follows:

COUNTIFS(age,"<=30")
COUNTIFS(age,">30",age,"<=45")
COUNTIFS(age,">45")

Notice the criteria used by COUNTIFS is exactly the same as that used by SUMIFS. The difference is that COUNTIFS does not have a sum_range argument.