In order to calculate the subtotal of invoices by age, the Excel SUMIF function can be used. This function, like in other previous sum categories, will use the same range and criteria but for subtotal invoices, there will be slight changes in the syntax used. The basic layout for this function is:
The formula to be used here is:
= SUMIF( age, criteria, amount)
- Age is the named range for age group
- Criteria are the specified limit for the age group
- Amount is the total range for the amount group
As stated above, in order to calculate the subtotal of invoices by age, the SUMIF function is used that will easily present us with the results.
In the following example, the formula used in cell H6 is:
=SUMIF(age, G6, amount)
Where age is the named range for E6:E12
And the amount is the named range for D6:D12
The SUMIF function just like sum processes and it uses the same three arguments, range, criteria and sum_range. In this case, we will specify the three arguments as:
- Range is the range of cells (E6: E12) that is aligned with the criteria in the G column.
- Criteria: G6 is the reference that contains the criteria.
- Sum_range: it is the range of cells (D6: D12) that is summed where the criteria is TRUE.
Figure 1: Example layout of using the SUMIF function for invoice subtotal