Formulas in Business Processes

ABS(value)

Returns the absolute value of the value. Text values are ignored.

=ABS(-35)

returns "35"

ADD(value1,value2)

Calculates the sum of two values.

=ADD(3, 4)

returns "7"

AVERAGE

=AVERAGE(3, 4, 5, 6)

returns "4.5"

AVERAGEA(value1,value2,...)

Returns the average value in a range of values, where "true" is treated as 1 and "false" as 0. Text values are treated as 0.

=AVERAGEA(TRUE, FALSE, "test", 4)

returns "1.25"

AVERAGEIF([value1, value2, ...], criterion)

Returns the average of a range of numbers depending on a criterion.

=AVERAGEIF([1, 2, 3], ">2")

returns "3"

AVG(value1,value2,...)

Calculates the average of a list of values. This function is supported with table/collection field variables.

=AVG(3, 4, 5, 6)

returns "4.5"

CEILING(value,factor)

Rounds value up to the nearest multiple of factor.

=CEILING(10.1, 2)

returns "12"

CODE(value)

Returns a number converted from a character based on the Unicode table.

=CODE("L")

returns "76"

COUNT(value1, value2, ...)

Counts the number of number values in the list. Non-numeric values are ignored.

=COUNT(3, 2, 7, 4, 6)

returns "5"  

COUNTIF([value1,value2,...],"condition")

Returns the number of values in the list that satisfy the condition.

=COUNTIF([1, 4, 6], ">1")

returns "2"

DIV(value1, value2)

Returns the quotient of two numbers.

=DIV(2,4)

returns "0.5"

=DIV(SUB(4,2), 2)

returns "1"

=DIV(SUM(2, 4),COUNT(2, 4))

returns the average of 2 and 4, which is 3

EQ(value1, value2)

Returns true if the two values are equal, false otherwise.

=EQ(2,3)

returns "FALSE"

EVEN(value)

Rounds the value up to the nearest even number (away from 0).

=EVEN(1)

returns "2"

EXP(exponent)

Returns Euler's number, e (~2.718) raised to the provided exponent.

=EXP(5)

returns "148.413159102577"

FIXED(value, significance)

Returns a number with a specified number of significant digits.

=FIXED(3.141592,3)

returns "3.142"

FLOOR(value,factor)

Rounds the value down to the nearest multiple of factor.

=FLOOR(11.9, 3)

returns "9"

GT(value1, value2)

Returns true if the first value is greater than the second, false otherwise.

=GT(2,2)

returns "FALSE"

GTE(value1, value2)

Returns true if the first value is greater than or equal to the second, false otherwise.

=GTE(2,2)

returns "TRUE"

DIV(value1, value2)

Returns the quotient of two numbers without the remainder.

=IDIV(3,2)

returns "1"

ISEVEN(value)

Returns true if the value is even.

= ISEVEN(2)

returns "TRUE"

ISODD(value)

Returns true if the value is odd.

=ISODD(2)

returns "FALSE"

LARGE(NumbersList, n)

Returns the nth largest value in the NumbersList.

=LARGE([10, 5, 7, 2], 2)

returns "7", because 7 is the second largest number

LN(Number)

Returns the natural logarithm of the value.

=LN(3)

returns "1.09861228866811"

LOG(Number, base)

Returns the logarithm of the value in the specified base.

=LOG(1000, 10)

returns "3"

LOG10(Number)

Returns the base 10 logarithm of the value.

=LOG10(1000)

returns "3"

LT(value1, value2)

Returns true if the first value is less than the second, and false otherwise.

=LT(2,2)

returns "FALSE"

LTE(value1, value2)

Returns true if the first value is less than or equal to the second, and false otherwise.

=LTE(2,2)

returns "TRUE"

MAX(value1,value2,...

Returns the maximum numerical value from the list of values, while ignoring values that are not numbers. This function supports table and collection field variables.

=MAX(1,2,4,1,2)

returns "4"

MAXA(value1, value2,...)

Returns the maximum value from the list of values, including text values and boolean values.

Text values are treated as 0.

Boolean value TRUE is treated as 1 and FALSE is treated as 0.

=MAXA(-1, 0, "abc", "TRUE")

returns "0"

=MAXA(-1, 0, "abc", TRUE)

returns "1", because TRUE does not have quotation marks

MEDIAN(value1, value2,...)

Returns the median (middle) value from the list of values when the list contains an odd number of values.

Returns the average of the two middle values when the list contains an even number of values.

=MEDIAN(3, 2, 5, 1, 4, 6)

returns "3.5"

MIN(value1,value2,...)

Returns the minimum value from a list of values, while ignoring values that are not numbers. This function supports table and collection field variables.

=MIN(10, 3, -5)

returns "-5"

MINA(value1, value2,...)

Returns the minimum of the list of values, including text values and boolean values.

Text values are treated as 0.

Boolean value TRUE is treated as 1 and FALSE is treated as 0.

=MINA(1, 3, "abc")

returns "0"

MINUS(value)

Returns the opposite of the current number sign.

=MINUS(1)

returns "-1"

=MINUS(-1)

returns "1"

MOD(value1, value2)

Returns the remainder of dividing the dividend by the divisor.

=MOD(33,24)

returns "9"

=MOD(variable1, 2)

returns "0" if variable1 is even and "1" if variable1 is odd. This formula is entered in the second of two fields, where the first field has variable1.

MODE(value1, value2,...)

Returns the most common value in the list of values.

=MODE(1, 3, 1, 4, 6)

returns "1"

MROUND(value, multiple)

Rounds the value to nearest multiple of multiple.

=MROUND(10.9, 3)

returns "12"

MULT(value1, value2)

Returns the product of two numbers.

=MULT(3, 4)

returns "12"

NEQ(value1, value2)

Returns true if the two values are not equal, false otherwise.

=NEQ(2,2)

returns "FALSE"

ODD(value)

Returns the value rounded to the nearest odd number (away from 0).

=ODD(2)

returns "3"

PI()

Returns the approximate value of Pi. There are no parameters for this formula.

=PI()

returns "3.14159265358979"

POWER(value, exponent)

Calculates raising value to the power of exponent.

=POWER(2,2)

returns "4"

PRODUCT(value1, value2,...)

Calculates the product of all numerical values in the list of values. Text values are ignored.

=PRODUCT(1, 2, 3, "abc", 4)

returns "24"

QUOTIENT(value1, value2)

Calculates the integer portion of dividing value1 by value2.

=QUOTIENT(3, 2)

returns "1"

ROUND(value, places)

Rounds the value to the specified number of decimal places. If the next most significant digit is greater than or equal to 5, the value will be rounded up. Otherwise, it will be rounded down.

=ROUND(543.21, 0)

returns "543"

ROUNDDOWN(value, places)

Rounds the value down to the specified number of decimal places.

=ROUNDDOWN(567.79, 0)

returns "567"

ROUNDUP(value, places)

Rounds the value up to the specified number of decimal places.

=ROUNDUP(543.21, 0)

returns "544"

SIGN(value)

Returns 1 if the value is greater than zero. Returns -1 if value is less than zero. Returns 0 if value equals zero.

=SIGN(-5)

returns "-1"

SQRT(value)

Calculates the square root of the value.

=SQRT(9)

returns "3"

=SQRT(ABS(-9))

returns "3"

SUB(value1, value2)

Returns the difference of two numbers.

=SUB(3,2)

returns "1"

SUM(value1,value2,...)

Calculates the sum of all number values in the list. This function supports table and collection field variables.

=SUM(1,2,3)

returns "6"

=SUM(VALUE(Single_Line_1), VALUE(Single_Line_2))

returns the sum of two single line fields.

=SUM(INDEX(Table_VariableName.Column_VarName1,ROW()),INDEX(Table_VariableName.Column_VarName2,ROW()))

after being placed in the third column of three columns, this formula returns the sum of the contents of the first two columns

SUMIF([value1,value2,...],"condition", [sum_range])

Filters the list for values that satisfy the condition and then calculates the sum of those values. If no sum range is specified, all values in the range will be summed.

= SUMIF([1, 2, 3, 4, 5], ">3")

returns "9"

=SUMIF(table_variable_name.column_variable_name, ">5", table_variable_name.column2_variable_name)

returns the sum of the values in column2 that are in the same row as the values greater than 5 in column1

SUMIFS(reference, range1, criterion1, range2, criterion2, ...)

Returns the sum of a range of values that meet multiple criteria in multiple ranges.

=SUMIFS([1,1,3], [3,2,3], ">2", [4,5,6], "<6")

returns "1", because the first value in range 1 is "3" and the first value in range 2 is "4". "3" fits criterion 1 because 3 is greater than 2 and "4" fits criterion 2 because 4 is less than 6, so "1" (in the reference) is a hit. Similarly, the second "1" is a hit. However, "3" is not a hit because 6 is not less than 6 in the second range.

TRUNC(value1, value2)

Truncates the first value to the specified value2 number of decimal places.

If the second value is set to 0 or is absent, truncates to a decimal integer.

=TRUNC(33.333, 2)

returns "33.33"

UPPER(text)

Converts the text to all uppercase letters  

  =UPPER("ucsd")

returns "UCSD"

VALUE(input)

Converts an input value to a number.

=VALUE("12:00:00")

returns "0.5"

=VALUE("1")

returns "1"

=VALUE("1/16/2017")

returns "42751"

CHAR(value)

Returns a character converted from a number based on the Unicode table.

=CHAR(76)

returns "L"

CODE(value)

Returns a number converted from a character based on the Unicode table.

=CODE("L")

returns "76"

CONCATENATE(value1, value2, ...)

Returns multiple strings appended together into one string.

=CONCATENATE("a", "b", "c")

returns "abc"

=CONCATENATE(CHAR(76), "aserfiche")

returns "Laserfiche"

EXACT(string1, string2)

Determines if two strings are identical. If the strings match, the function will return "true". If the strings don't match, the function will return "false."

=EXACT("dog", "cat")

returns "FALSE"

FIND(target text, text to search, starting character)

  Finds the position of the target text in the text to search. The search will start at the starting character number within the text to search.

  =FIND("type", "prototype", 1)

returns "6"

JOIN(delimiter, array)

Concatenates the values in the given array into a string, separated by a delimiter.

=JOIN("", ["L", "a", "s", "e", "r"])

returns "Laser"

LEFT(string, [characters])

Returns the left-most character in the string. Where you specify a number of characters, the function returns the characters from the left.

=LEFT("Laserfiche", 2)

returns "La"

LEN(text)

  Returns the length of the text

=LEN("Laserfiche")

returns "10"  

LOWER(text)

  Converts text to lowercase

  =LOWER("POLICY")

returns "policy"

MAXA(value1, value2,...)

Returns the maximum value from the list of values, including text values and boolean values.

Text values are treated as 0.

Boolean value TRUE is treated as 1 and FALSE is treated as 0.

=MAXA(-1, 0, "abc", "TRUE")

returns "0"

=MAXA(-1, 0, "abc", TRUE)

returns "1", because TRUE does not have quotation marks

MID(text, starting character, length)

Extracts part of a string of text, starting at the starting character and ending after the specified length is reached.

  =MID("PO 1234 EZ", 4, 4)

returns "1234"

MINA(value1, value2,...)

Returns the minimum of the list of values, including text values and boolean values.

Text values are treated as 0.

Boolean value TRUE is treated as 1 and FALSE is treated as 0.

=MINA(1, 3, "abc")

returns "0"

PROPER(text)

Returns the input string with the first character in each word capitalized.

=PROPER("laserfiche")

returns "Laserfiche"

REPLACE(old_string, start_position, count, new_string)

Returns new string based on a modification to an old string.

=REPLACE("Laserfiche", 6, 5, "fish")

returns "Laserfish"

REPT(text, number)

  Returns the text repeated the specified number of times

  =REPT("Laserfiche", 2)

returns "LaserficheLaserfiche"

RIGHT(value, n)

Returns a substring with n characters starting from the end of a string.

=RIGHT("Laserfiche", 5)

returns "fiche"

SPLIT(textValue, textSplit)

Returns an array with each piece of textValue after splitting it based on the given splitting text.

=SPLIT("Text.Split.Up", ".")

returns "Text,Split,Up"

SUBSTITUTE(text, target text, replacement text, number)

  Replaces existing text with new text.

The number parameter is the number of replacements that you want to make. If you don't specify a number, all instances of the target text will be replaced.

  =SUBSTITUTE("Employees: Susan Marks, Paul Smith, Eric Chen", "Marks", "Johnson")

returns "Employees: Susan Johnson, Paul Smith, Eric Chen"

T(value)

Returns a string as text.

=T("1")

returns "1"

=T(1)

returns nothing

TEXT(number)

Converts the value to a string and returns it.

=TEXT(ABS(3-5))

returns "2"

TRIM(text)

  Removes spaces before and after the text.

  =TRIM(" Paul ")

returns "Paul"

UPPER(text)

Converts the text to all uppercase letters  

  =UPPER("ucsd")

returns "UCSD"

ADD_MONTHS(date, months)

Adds months to a date object. Non-integer values for month will be added in days, hours, minutes, and seconds. A date object is returned

=ADD_MONTHS(DATE(2015,8,11),4)

returns "42349"

DATE(year,month,day)

Returns a unique number representing a specified date.

Note: When used with a date-based variable or field, the date will be formatted as defined by the field or variable.

=DATE(1985,12,2)

returns "31383"

DATEDIF(start_date, end_date, time_unit)

Calculates the difference between two dates based on a time unit.

=DATEDIF(DATE(2017, 1, 2), DATE(2017, 1, 14), "M")

returns "0"

=DATEDIF(DATE(2017, 1, 2), DATE(2017, 1, 14), "D")

returns "12"

DATEVALUE(dateString)

Converts a date stored as text to a serial number of the date.

=DATEVALUE("2017-1-14")

returns "42749"

DAY(dateValue)

Returns the day of a given date (1-31).

=DAY(TODAY())

returns "2" on January 2nd

=DAY(DATE(1985,12,2))

returns "2"

DAYS360(start_date, end_date)

Returns the number of days between two given dates

=DAYS360(DATE(2017, 1, 2), DATE(2018, 1, 3))

returns "361", because this is 1 day after a full year

EDATE(startDate, months)

Returns the serial number of the date that is the indicated number of months before or after the start date.

Note: When used with a date-based variable or field, the date will be formatted as defined by the field or variable.

=EDATE(DATE(2017, 1, 2), 1)

returns "42768"

EOMONTH(startDate, months)

Returns the serial number for the last day of the month that is the indicated number of months before or after startDate. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.

Note: When used with a date-based variable or field, the date will be formatted as defined by the field or variable.

=EOMONTH(DATE(2017, 1, 2), 1)

returns "42794"

=EOMONTH(1/2/2017, -1)

returns "42735"

HOUR(dateValue)

Returns the hour of a given date (0-23).

=HOUR(TIME(11,40,59))

returns "11"

MINUTE(timeValue)

Returns the minute of a given time value.

=MINUTE(TIME(12,30,01))

returns "30"

MONTH(dateValue)

Returns the month of a given date (1-12).

=MONTH(DATE(2017, 1, 20))

returns "1"

NETWORKDAYS(startDate, endDate, [holidays], [weekDays])

Returns the whole number of work days between two dates.

=NETWORKDAYS(DATE(2017,1,6),DATE(2017,1,14))

returns "6"

NOW()

Returns the current date and time. Useful for functions/operators that work with dates.

=SECOND(NOW())

returns "42"

SECOND(timeValue)

Returns the second of a given time. Takes on values from 0 to 59.

=SECOND(TIME(12,01,09))

returns "9"

TIME(hours, minutes, seconds)

Returns the time as a fraction of the day. Values are not limited to {0,24},{0,60}, {0,60} and carry over (e.g., "25:00:00" is equivalent to "1:00:00").

=TIME(21, 20, 03)

returns "0.888923611111111"

TIMEVALUE(time)

Returns the time as a fraction of the day. Inputs can be in the form HH:MM, HH:MM:SS, YYYY-MM-DD HH:MM:SS, or YYYY-MM-DD HH:MM.

=TIMEVALUE("12:15 PM")

returns "0.510416666666667"

TODAY()

Returns the current date (ignores current time).

=TODAY()

returns the current date as a serial number.

WEEKDAY(date, [type])

Returns the day of the week of the specified date as a number. Type can be set to 1, 2, or 3 and determines whether the first day of the week is Sunday or Monday.

Type=1: Sunday to Saturday => 1 to 7

Type=2: Monday to Sunday => 1 to 7

Type=3: Monday to Sunday => 0 to 6

=WEEKDAY("4/15/2016", 3)

returns "4"

WEEKNUM(date, mode)

Determines the week number of the year for a given date.

The mode parameter is optional and, if omitted, is "1".

1 = Sunday, system 1

2 = Monday, system 1

11 = Monday, system 1

12 = Tuesday, system 1

13 = Wednesday, system 1

14 = Thursday, system 1

15 = Friday, system 1

16 = Saturday, system 1

17 = Sunday, system 1

=WEEKNUM(DATE(2017,1,8))

returns "2" because the date is in the first week of the year

=WEEKNUM(DATE(2017,1,8), 2)

returns "1" because the "2" indicates the counting starts on a Monday.

WORKDAY(startDay, numDays)

Returns the end date after a specified number of working days as a serial number.

=WORKDAY(DATE(2017,2,16), 1)

returns "42783", while

=DATE(2017,2,16)

returns "42782"

YEAR(dateValue)

Returns the year of a given date.

=YEAR(DATE(2017,1,16))

returns "2017"

YEARFRAC(startDate, endDate, [basis])

Computes the fraction of the number of years between a startDate and endDate. Basis{0,1} uses US (NASD) 30/360 and Actual/360 to compute days.

=YEARFRAC(DATE(2017,1,17),DATE(2017,1,18))

returns "0.00277777777777778"  

AND(value1,value2,...)

Returns true if all the provided arguments are true. Otherwise, returns false.

=AND(TRUE, FALSE)

returns "FALSE"

AVERAGEA(value1,value2,...)

Returns the average value in a range of values, where "true" is treated as 1 and "false" as 0. Text values are treated as 0.

=AVERAGEA(TRUE, FALSE, "test", 4)

returns "1.25"

COUNTIF([value1,value2,...],"condition")

Returns the number of values in the list that satisfy the condition.

=COUNTIF([1, 4, 6], ">1")

returns "2"

IF(booleanValue,value1,value2)

If booleanValue is true, returns value1. Otherwise, returns value2.

=IF(11>10,"Accepted","Denied")

returns "Accepted"

=IF(Variable1<1,0,IF(Variable1<20,Number,IF(Variable1<60,PRODUCT(Variable1,2),PRODUCT(Variable1,3))))

returns variable1 multiplied by 1 IF the number is between 1 and 19, by 2 IF the number is between 20 and 59, and by 3 IF the number is greater than 60. This formula is entered in the second of two fields, where the first field has variable1.

MAXA(value1, value2,...)

Returns the maximum value from the list of values, including text values and boolean values.

Text values are treated as 0.

Boolean value TRUE is treated as 1 and FALSE is treated as 0.

=MAXA(-1, 0, "abc", "TRUE")

returns "0"

=MAXA(-1, 0, "abc", TRUE)

returns "1", because TRUE does not have quotation marks

MINA(value1, value2,...)

Returns the minimum of the list of values, including text values and boolean values.

Text values are treated as 0.

Boolean value TRUE is treated as 1 and FALSE is treated as 0.

=MINA(1, 3, "abc")

returns "0"

NOT(value)

Returns the opposite of the value.

=NOT(FALSE)

returns "TRUE"

OR(value1, value2, ...)

Returns true if any of the arguments are true, false if all of them are false.

=OR(TRUE, FALSE, TRUE)

returns "TRUE"

SUMIF([value1,value2,...],"condition", [sum_range])

Filters the list for values that satisfy the condition and then calculates the sum of those values. If no sum range is specified, all values in the range will be summed.

= SUMIF([1, 2, 3, 4, 5], ">3")

returns "9"

=SUMIF( table_variable_name.column_variable_name, ">5", [table_variable_name.column2_variable_name])

returns the sum of the values in column2 that are in the same row as the values greater than 5 in column1

SUMIFS(reference, range1, criterion1, range2, criterion2, ...)

Returns the sum of a range of values that meet multiple criteria in multiple ranges.

=SUMIFS([1,1,3], [3,2,3], ">2", [4,5,6], "<6")

returns "1", because the first value in range 1 is "3" and the first value in range 2 is "4". "3" fits criterion 1 because 3 is greater than 2 and "4" fits criterion 2 because 4 is less than 6, so "1" (in the reference) is a hit. Similarly, the second "1" is a hit. However, "3" is not a hit because 6 is not less than 6 in the second range.

AND(value1,value2,...)

Returns true if all the provided arguments are true. Otherwise, returns false.

=AND(TRUE, FALSE)

returns "FALSE"

AVG(value1,value2,...)

Calculates the average of a list of values. This function is supported with table/collection field variables.

=AVG(3, 4, 5, 6)

returns "4.5"

CHOOSE(index, val1, val2, ...)

Returns the corresponding value at the given index, if it exists. Input values may be any type (array, number, text, etc.)

=CHOOSE(3, 10, 11, 12)

returns "12", because the index "3" has the value "12"

CHOOSEA(index, val1, val2, ...)

Returns the corresponding value at the given index, if it exists. If more than one input exists in one value, the function flattens the input.

=CHOOSEA(2, 1, [2,3], 4)

returns "2" because the list "1, [2, 3], 4" is flattened to be "1, 2, 3, 4".

COLUMN()

Returns the column index of a cell in a table, where a cell in the first column has a value of 1.

=COLUMN()

returns "1" for a cell in column 1

=COLUMN()=0

returns "TRUE" if the column is empty

COUNT(value1, value2, ...)

Counts the number of number values in the list. Non-numeric values are ignored.

=COUNT(3, 2, 7, 4, 6)

returns "5"  

COUNTIF([value1,value2,...],"condition")

Returns the number of values in the list that satisfy the condition.

=COUNTIF([1, 4, 6], ">1")

returns "2"

INDEX(column, row)

Returns an item in an array.

=INDEX(Table.Column_1, 3)

returns the third item in column 1 of a table.

JOIN(delimiter, array)

Concatenates the values in the given array into a string, separated by a delimiter.

=JOIN("", ["L", "a", "s", "e", "r"])

returns "Laser"

MAX(value1,value2,...

Returns the maximum numerical value from the list of values, while ignoring values that are not numbers. This function supports table and collection field variables.

=MAX(1,2,4,1,2)

returns "4"

MAXA(value1, value2,...)

Returns the maximum value from the list of values, including text values and boolean values.

Text values are treated as 0.

Boolean value TRUE is treated as 1 and FALSE is treated as 0.

=MAXA(-1, 0, "abc", "TRUE")

returns "0"

=MAXA(-1, 0, "abc", TRUE)

returns "1", because TRUE does not have quotation marks

MIN(value1,value2,...)

Returns the minimum value from a list of values, while ignoring values that are not numbers. This function supports table and collection field variables.

=MIN(10, 3, -5)

returns "-5"

MINA(value1, value2,...)

Returns the minimum of the list of values, including text values and boolean values.

Text values are treated as 0.

Boolean value TRUE is treated as 1 and FALSE is treated as 0.

=MINA(1, 3, "abc")

returns "0"

MODE(value1, value2,...)

Returns the most common value in the list of values.

=MODE(1, 3, 1, 4, 6)

returns "1"

OR(value1, value2, ...)

Returns true if any of the arguments are true, false if all of them are false.

=OR(TRUE, FALSE, TRUE)

returns "TRUE"

PRODUCT(value1, value2,...)

Calculates the product of all numerical values in the list of values. Text values are ignored.

=PRODUCT(1, 2, 3, "abc", 4)

returns "24"

ROW()

Returns the row number of a cell in a table, where a cell in the first row has a value of 1.

=ROW()

returns "1" for a cell in row 1

=ROW()=0

returns "TRUE" if the row is empty

SUM(value1,value2,...)

Calculates the sum of all number values in the list. This function supports table and collection field variables.

=SUM(1,2,3)

returns "6"

=SUM(VALUE(Single_Line_1), VALUE(Single_Line_2))

returns the sum of two single line fields.

=SUM(INDEX(Table_VariableName.Column_VarName1,ROW()),INDEX(Table_VariableName.Column_VarName2,ROW()))

after being placed in the third column of three columns, this formula returns the sum of the contents of the first two columns

SUMIF([value1,value2,...],"condition", [sum_range])

Filters the list for values that satisfy the condition and then calculates the sum of those values. If no sum range is specified, all values in the range will be summed.

= SUMIF([1, 2, 3, 4, 5], ">3")

returns "9"

=SUMIF( table_variable_name.column_variable_name, ">5", [table_variable_name.column2_variable_name])

returns the sum of the values in column2 that are in the same row as the values greater than 5 in column1

SUMIFS(reference, range1, criterion1, range2, criterion2, ...)

Returns the sum of a range of values that meet multiple criteria in multiple ranges.

=SUMIFS([1,1,3], [3,2,3], ">2", [4,5,6], "<6")

returns "1", because the first value in range 1 is "3" and the first value in range 2 is "4". "3" fits criterion 1 because 3 is greater than 2 and "4" fits criterion 2 because 4 is less than 6, so "1" (in the reference) is a hit. Similarly, the second "1" is a hit. However, "3" is not a hit because 6 is not less than 6 in the second range.