Exceljet

Quick, clean, and to the point

Subtotal invoices by age

Excel formula: Subtotal invoices by age
Generic formula 
=SUMIF(amount,age,criteria)
Summary 

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.

Author 
Dave Bruns

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.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.