Where data is an Excel Table in the range B5:D16. Note: With an Excel Table, the formula will automatically update if data is added or removed from the table.
SUMIFS function
The SUMIFS function can sum values in ranges based on multiple criteria. The basic function signature for SUMIFS looks like this: In this case, we need to configure SUMIFS to sum values in the Amount column based on two criteria: Conditions are supplied to SUMIFS as range/criteria pairs, so each condition will be composed of two arguments. To start off, we provide the sum_range to SUMIFS, which contains the values we want to sum: Next, add the first condition, which is that amounts need to be greater than zero (the value in cell F5): Here we provide data[Amount] as criteria_range1, and the “>"&F5 for criteria1. Notice we need to concatenate the cell reference to the logical operators, and the operators are entered as text*, enclosed in double quotes (”"). Next, we need to add the second condition, for amounts less than or equal to 500 (the value in cell E5): As before, we need to concatenate the operator as text to cell C5. When we enter this formula, it returns 1400, the total of all amounts greater than 0 and less than or equal to 500. As the formula is copied down, it returns a new total in each row, based on the values in columns F and G. The structured reference data[Amount] behaves like an absolute reference and does not change. The references to F5 and G5 are relative and change at each new row.
- Note: SUMIFS is in a group of functions that split criteria into two parts. As a result, the syntax used for operators is different from other functions. See this article for details.
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.
