Conditional sums and totals
Add up only what qualifies, summarise a list with one function whose summary you choose by number, and total the squares of a list.
Syntax
Write the lists in square brackets in the call, with the conditions as text such as ">100".
SUMIF(range, criteria, [sum_range])
SUMIFS(sum_range, criteria_range, criteria)
SUBTOTAL(function_num, lists)
AGGREGATE(function_num, options, lists)
SUMSQ(values)
SUMX2MY2(x, y)
SUMX2PY2(x, y)
SUMXMY2(x, y)Parameters and arguments
Arguments are written in brackets, separated by commas. An argument in square brackets is optional.
SUMIF
Required: Yesrange
Required: Yescriteria
Required: Nosum_rangeSUMIFS
Required: Yessum_range
Required: Yescriteria_range
Required: YescriteriaSUBTOTAL
Required: Yesfunction_num
Required: YeslistsAGGREGATE
Required: Yesfunction_num
Required: Yesoptions
Required: YeslistsSUMSQ
Required: YesvaluesSUMX2MY2
Required: Yesx
Required: YesySUMX2PY2
Required: Yesx
Required: YesySUMXMY2
Required: Yesx
Required: Yesy
Examples
Order values above 100, revenue where a score is at least 3, an exact match, and a total that must meet two conditions at once.
Return type and behaviour
Each function returns one value. The table shows its type, what it does and one way to write it; the examples use the functions in realistic statements.
| Function | Returns | Behaviour | Form |
|---|---|---|---|
SUMIF | decimal | The sum of the items where the condition holds | SUMIF([1, 2, 3], ">1") |
SUMIFS | decimal | The sum of the items where every condition holds | SUMIFS([10, 20, 30], [1, 2, 3], ">1") |
SUBTOTAL | number | The chosen summary of the lists. A list of whole numbers gives whole-number sums, products, counts, largest and smallest | SUBTOTAL(9, [1, 2, 3]) |
AGGREGATE | number | The chosen summary of the lists | AGGREGATE(12, 6, [1, 2, 3]) |
SUMSQ | decimal | The sum of the squares of every value | SUMSQ(3, 4) |
SUMX2MY2 | decimal | The sum of x squared less y squared, pair by pair | SUMX2MY2([3, 4], [1, 2]) |
SUMX2PY2 | decimal | The sum of x squared plus y squared, pair by pair | SUMX2PY2([3, 4], [1, 2]) |
SUMXMY2 | decimal | The sum of the squared differences, pair by pair | SUMXMY2([3, 4], [1, 2]) |
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-5001 | The function was given the wrong number of arguments. The message names the function and the counts | Give it the arguments listed under Parameters |
HTQL-1004 Unsupported function | The name is not a function this engine runs | Check the name and its spelling against this entry |
HTQL-2001 | SUMIFS was given lists of different length, or SUBTOTAL a function number outside 1 to 11 | Match the lists, or use 1 to 11 |
HTQL-2002 | A SUMIF or SUMIFS condition is not a comparison with a number | Write a number after the comparison |
HTQL-8001 | SUBTOTAL was given an empty list, or a pair function two lists of different length (#N/A) | Give lists with values, of the same length |
HTQL-8003 | AGGREGATE was given a function number outside 1 to 19 or options outside 0 to 7 (#VALUE!) | Use a number in range |
Limitations
- The conditions of
SUMIFandSUMIFScompare numbers: a comparison (>,>=,<,<=,=,<>) followed by a number, or a plain number. A condition on text such as"red"is refused withHTQL-2002. - Every list in
SUMIFSmust be as long as the sum range, elseHTQL-2001. Keep the sum range ofSUMIFas long as its range. SUBTOTALtakes the numbers 1 to 11 only; the 101 to 111 forms are refused withHTQL-2001. An empty list answersHTQL-8001.AGGREGATEtakes 1 to 19, and an empty list answersHTQL-8002. The options number does not skip anything, because a list holds no hidden rows or errors.SUMX2MY2,SUMX2PY2andSUMXMY2need two lists of the same length, elseHTQL-8001.
Permissions and security
These functions read nothing from your tables and write nothing.
When a function is applied to a column or a variable, the statement that reads the data needs read access, as its own entry says.
Best practices
- Use SUMIFS when a total depends on more than one condition, and name each list in a variable first when you reuse it.
- Use AGGREGATE when one place has to switch between summaries, such as a report line chosen by a setting.