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

    range

    Required: Yes
  • criteria

    Required: Yes
  • sum_range

    Required: No
  • SUMIFS

    sum_range

    Required: Yes
  • criteria_range

    Required: Yes
  • criteria

    Required: Yes
  • SUBTOTAL

    function_num

    Required: Yes
  • lists

    Required: Yes
  • AGGREGATE

    function_num

    Required: Yes
  • options

    Required: Yes
  • lists

    Required: Yes
  • SUMSQ

    values

    Required: Yes
  • SUMX2MY2

    x

    Required: Yes
  • y

    Required: Yes
  • SUMX2PY2

    x

    Required: Yes
  • y

    Required: Yes
  • SUMXMY2

    x

    Required: Yes
  • y

    Required: Yes

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.

FunctionReturnsBehaviourForm
SUMIFdecimalThe sum of the items where the condition holdsSUMIF([1, 2, 3], ">1")
SUMIFSdecimalThe sum of the items where every condition holdsSUMIFS([10, 20, 30], [1, 2, 3], ">1")
SUBTOTALnumberThe chosen summary of the lists. A list of whole numbers gives whole-number sums, products, counts, largest and smallestSUBTOTAL(9, [1, 2, 3])
AGGREGATEnumberThe chosen summary of the listsAGGREGATE(12, 6, [1, 2, 3])
SUMSQdecimalThe sum of the squares of every valueSUMSQ(3, 4)
SUMX2MY2decimalThe sum of x squared less y squared, pair by pairSUMX2MY2([3, 4], [1, 2])
SUMX2PY2decimalThe sum of x squared plus y squared, pair by pairSUMX2PY2([3, 4], [1, 2])
SUMXMY2decimalThe sum of the squared differences, pair by pairSUMXMY2([3, 4], [1, 2])
Errors and exceptions
ErrorWhat happenedHow to recover
HTQL-5001The function was given the wrong number of arguments. The message names the function and the countsGive it the arguments listed under Parameters
HTQL-1004 Unsupported functionThe name is not a function this engine runsCheck the name and its spelling against this entry
HTQL-2001SUMIFS was given lists of different length, or SUBTOTAL a function number outside 1 to 11Match the lists, or use 1 to 11
HTQL-2002A SUMIF or SUMIFS condition is not a comparison with a numberWrite a number after the comparison
HTQL-8001SUBTOTAL 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-8003AGGREGATE 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 SUMIF and SUMIFS compare numbers: a comparison (>, >=, <, <=, =, <>) followed by a number, or a plain number. A condition on text such as "red" is refused with HTQL-2002.
  • Every list in SUMIFS must be as long as the sum range, else HTQL-2001. Keep the sum range of SUMIF as long as its range.
  • SUBTOTAL takes the numbers 1 to 11 only; the 101 to 111 forms are refused with HTQL-2001. An empty list answers HTQL-8001.
  • AGGREGATE takes 1 to 19, and an empty list answers HTQL-8002. The options number does not skip anything, because a list holds no hidden rows or errors.
  • SUMX2MY2, SUMX2PY2 and SUMXMY2 need two lists of the same length, else HTQL-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.