Subtotal By Invoice Number

Explanation

This formula uses COUNTIF with an expanding range to first check if the current row is the first occurrence of a given invoice number:

COUNTIF($B$5:B5,B5)=1

This expression only returns TRUE when this is the first occurrence of a given invoice number. If so, a SUMIF calculation is run:

SUMIF($B:$B,B5,$D:$D)

Here, SUMIF generates a total sum by invoice number, using the amounts in column D. If the count is not 1, the formula simply returns an empty string ("")

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")

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.