Subtotal Invoices By Age Excel Formula

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.

SUMIFS function

The SUMIFS function is designed to sum cells that meet multiple criteria. SUMIFS takes at least three arguments like this: Notice sum_range appears first. Additional criteria are added in range/criteria pairs like this: The formulas used to sum invoices by age in I5:I7 are as follows: 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: The formulas used to count invoices by age in H5:H7 are as follows: 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.

Dave Bruns

Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.