# PRODUCTIF function

**PRODUCTIF**(Array, Condition)

**PRODUCTIF**(Array; Condition)

## Array

The array containing the numbers to multiply together.

## Condition

A text string specifying the condition, a number which array elements must be equal to or a formula fragment specifying the condition. Use a text string like ">3"">3" to specify a condition.

If a text string is given, the following operators are supported:
< (less than), <= (less than or
equal to), > (greater than), >= (greater than
or equal to), = and == (equality),
<> and !=
(inequality). `""`

means "only blank values,"
`"<>"`

and `"!="`

mean "only non-blank
values."

If a formula fragment is given, it must return a logical value (TRUE or
FALSE) specifying whether the element under consideration should be
included. To do its work, it has access to the values
`Element`

(the array element currently being tested),
`Index`

(the numeric position of the array element currently
being tested, starting at 1) and `Source`

, the source array.

## Returns

The product of the array elements that satisfy a condition.

Returns the product of the array elements that satisfy a condition (multiplying them together). PRODUCTIF({ 3, 4, 5 }, ">3")PRODUCTIF({ 3; 4; 5 }; ">3") returns 20 (4 * 54 * 5) and not 60 (3 * 4 * 53 * 4 * 5), as only 4 and 5 are greater than 3, as specified by the ">3"">3" parameter.

Use & to reference values that
reside elsewhere. This formula only considers those array elements which are
greater than the value of *Field1*:

PRODUCTIF can use separate arrays, of equal size, for determining which elements to consider and for determining which numbers to multiply together. This version is described further down. Use PRODUCTIFS to use multiple conditions (or use PRODUCTIF with a formula fragment and &&, described below).

This function is specific to Calcapp.

## Writing conditions

PRODUCTIF supports the following operators: < (less than), <= (less than or equal to), > (greater than), >= (greater than or equal to), = and == (equality), <> and != (inequality).

If the second parameter is set to `""`

, only blank values are
considered. If it is set to `"<>"`

or `"!="`

, all
non-blank values are considered.

If the operator is left out, `"="`

is assumed. As a result, these
formulas are equivalent:

The language settings of the app are taken into account when parsing numbers that are part of the condition text string, meaning that an app configured to use German should use a decimal comma (",") as a decimal separator and an app configured to use Australian English should use a decimal point ("."). Thousands separators should not be used and negative numbers should use a leading minus sign ("-").

## Specifying the condition using a formula fragment

The second parameter can also be a formula fragment. These formulas are equivalent, with the second one using a formula fragment:

Using a formula fragment gives you access to the full power of the formula language. Consider this formula:

The formula above returns 5, because only array elements are considered which are greater than 3 and are odd numbers, which disqualifies 3 and 4, leaving only 5.

&& means logical "and," || means logical "or" and ! means logical negation.

This formula returns 60 (3 * 4 * 53 * 4 * 5), as 3, 4 and 5 are all considered:

3, 4 and 5 are all considered due to the || operator, which causes all array elements to be considered which are either greater than 3 or are odd.

This formula returns 4, as only 4 is both greater than 3 and is "not odd" (even):

! in !ISODD(Element)!ISODD(Element) negates the value returned from ISODD, causing TRUE to become FALSE and vice versa.

The formula fragment is run once for every array element to be tested, and is
expected to return TRUE if the element should be considered, and FALSE
otherwise. To do its work, it has access to the values `Element`

(the array element currently being tested), `Index`

(the numeric
position of the array element currently being tested, starting at 1) and
`Source`

, the source array.

## Using FILTER, PRODUCT and REDUCE instead of PRODUCTIF

The work PRODUCTIF performs can be divided into two parts: filtering out unwanted array elements and then returning the product of the array elements which made the cut. As such, these formulas are equivalent:

The version using FILTER provides more flexibility, as the final step (multiplying the remaining numbers together through PRODUCT) can be replaced by another function (like SUM, which adds the remaining numbers together), independent of the filtering logic.

For even more flexibility, consider replacing PRODUCT with REDUCE. Refer to the example below for more information.

## Examples

Returns 20 (4 * 54 * 5). It does not return 60 (3 * 4 * 53 * 4 * 5), as only 4 and 5 are greater than 3, as specified by the ">3"">3" parameter.

Returns the product of all fields in the Field1:Field10Field1:Field10 range, where only fields whose values are greater than 1 are considered.

Determines which of the numbers of the { 3, 4, 5 }{ 3; 4; 5 } array are
greater than the value of *Field1* and returns the product of
those numbers. If Field1.ValueField1,Value is 3, then 20
is returned (4 *
54 *
5).

Returns 4, as only 4 in the array { 3, 4, 5 }{ 3; 4; 5 } satisfies the "=4""=4" condition.

Returns 4, as only 4 in the array { 3, 4, 5 }{ 3; 4; 5 } satisfies the "4""4" condition. Equality is implied when no operator is specified.

Returns 4, as only 4 in the array { 3, 4, 5 }{ 3; 4; 5 } satisfies the 44 condition. When a plain value is given as the second parameter, equality is implied. If only values equal to another value should be considered, using a plain value instead of a value enclosed in a text string is preferable, as errors can be detected at an earlier stage.

Returns 1.56 (1.2 * 1.31,2 * 1,3). It does not return 1.716 (1.1 * 1.2 * 1.31,1 * 1,2 * 1,3), as only 1.2 and 1.3 are greater than 1.1, as specified by the ">1.1"">1.1" parameter. A decimal separator appropriate for the language of the app should be used. If that language is French, for instance, a decimal comma should be used ("1,1" instead of "1.1").

Returns 20 (4 *
54 *
5). It does not return 60 (3 * 4 * 53 * 4 * 5), as only 4 and
5 are greater than 3, as specified by the Element > 3Element > 3 parameter. This
example uses a formula fragment instead of a text string. It is invoked
once for every array element considered, and is expected to return TRUE
if the value should be considered and FALSE otherwise. To do its work, it
has access to the `Element`

value, which is the value under
consideration.

Returns 5, as only 5 is both greater than 3 and is an odd number. Formula fragments have access to the full power of the formula language, enabling complex conditions. && means logical "and," || means logical "or" and ! means logical negation.

Returns 20 (4 * 54 * 5). It does not return 60 (3 * 4 * 53 * 4 * 5), as only 4 and 5 are greater than 3, as specified by the Element > 3Element > 3 parameter. FILTER first removes array elements which are not greater than 3 (specifically, the element 3). Finally, PRODUCT multiplies the remaining numbers (4 and 5) together and returns the result.

Returns 20 (4 * 54 * 5). It does not return 60 (3 * 4 * 53 * 4 * 5), as only 4 and 5 are greater than 3, as specified by the Element > 3Element > 3 parameter to FILTER. REDUCE is a more flexible alternative to functions like PRODUCT. Refer to its documentation to learn how it works.

**PRODUCTIF**(ConditionArray, Condition, TargetArray)

**PRODUCTIF**(ConditionArray; Condition; TargetArray)

## ConditionArray

The array containing the values to test. Only the numbers of the second
array, `targetArray`

, which correspond to the elements of
this array which satisfy the condition, are used in the final
calculation. Two elements are said to *correspond* to each other
if they are in the same position in their respective arrays.

## Condition

A text string specifying the condition, a number which array elements must be equal to or a formula fragment specifying the condition. Use a text string like ">3"">3" to specify a condition.

If a text string is given, the following operators are supported:
< (less than), <= (less than or
equal to), > (greater than), >= (greater than
or equal to), = (equality, case-insensitive for
text), == (equality, case-sensitive
for text), <> (inequality,
case-insensitive for text) and != (inequality,
case-sensitive for text). `""`

means "only blank values,"
`"<>"`

and `"!="`

mean "only non-blank
values."

If a formula fragment is given, it must return a logical value (TRUE or
FALSE) specifying whether the element under consideration should be
included. To do its work, it has access to the values
`Element`

(the array element currently being tested),
`Index`

(the numeric position of the array element currently
being tested, starting at 1) and `Source`

, the first array.

## TargetArray

The array containing the numbers to multiply together. Only numbers
corresponding to elements of the first array which satisfy the given
condition are considered. Two elements are said to *correspond*
to each other if they are in the same position in their respective
arrays.

## Returns

The product of all array elements which satisfy a condition.

Returns the product of all array elements which satisfy a condition (multiplying them together). PRODUCTIF({ 3, 4, 5 }, ">3", { 30, 40, 50 })PRODUCTIF({ 3; 4; 5 }; ">3"; { 30; 40; 50 }) returns 2,000 (40 * 5040 * 50) and not 60,000 (30 * 40 * 5030 * 40 * 50), as only 4 and 5 of the first array are greater than 3, as specified by the ">3"">3" parameter.

Use & to reference values that
reside elsewhere. This formula only considers those elements of the second
array which are greater than the value of *Field1*:

PRODUCTIF can use the same array for determining which elements to consider and for determining which numbers to multiply together. This version is described further up. Use PRODUCTIFS to use multiple conditions (or use PRODUCTIF with a formula fragment and &&, described below).

This function is specific to Calcapp.

## Beyond numbers

The first array does not need to be a number array. This formula returns 1,200 (30 * 4030 * 40), as only 30 and 40 correspond to "Eve" in the first array:

## Writing conditions

PRODUCTIF supports the following operators: < (less than), <= (less than or equal to), > (greater than), >= (greater than or equal to), = (equality, case-insensitive for text), == (equality, case-sensitive for text), <> (inequality, case-insensitive for text) and != (inequality, case-sensitive for text).

If the second parameter is set to `""`

, only the blank values of
the second array are considered. If it is set to `"<>"`

or
`"!="`

, all non-blank values are considered.

If the operator is left out, `"="`

is assumed. As a result, these
formulas are equivalent:

The language settings of the app are taken into account when parsing numbers that are part of the condition text string, meaning that an app configured to use German should use a decimal comma (",") as a decimal separator and an app configured to use Australian English should use a decimal point ("."). Thousands separators should not be used and negative numbers should use a leading minus sign ("-").

## Specifying the condition using a formula fragment

The second parameter can also be a formula fragment. These formulas are equivalent, with the second one using a formula fragment:

Using a formula fragment gives you access to the full power of the formula language. Consider this formula:

The formula above returns 50, because only array elements are considered which are greater than 3 and are odd numbers, which disqualifies 3 and 4, leaving only 5 (corresponding to 50 in the second array).

&& means logical "and," || means logical "or" and ! means logical negation.

This formula returns 60,000 (30 * 40 * 5030 * 40 * 50), as 3, 4 and 5 are all considered:

3, 4 and 5 are all considered due to the || operator, which causes all array elements to be considered which are either greater than 3 or are odd (3, 4 and 5 correspond to 30, 40 and 50 in the second array).

This formula returns 40, as only 4 is both greater than 3 and is "not odd" (even):

! in !ISODD(Element)!ISODD(Element) negates the value returned from ISODD, causing TRUE to become FALSE and vice versa.

The formula fragment is run once for every array element to be tested, and is
expected to return TRUE if the element should be considered, and FALSE
otherwise. To do its work, it has access to the values `Element`

(the element of the first array currently being tested), `Index`

(the numeric position of the array element currently being tested, starting
at 1) and `Source`

, the first array.

## Using wildcards in the condition to match text flexibly

When the first array contains text strings, the condition can use special characters to match multiple text strings. This formula returns 1,000 (10 * 10010 * 100), because both "car" and "cat" match the wildcard "ca?":

`?`

matches any character. "ca?" matches not only "car" and "cat",
but also "cab", "can" and "cap". However, it does not match "card", because
`?`

matches exactly one character, nor does it match "ca".

`*`

matches no character, a single character, or multiple
characters. As such, "ca*" matches everything that "ca?" matches, but also
"card", "ca" and "Canada" (matching is case-insensitive).

`?`

and `*`

can be combined in the same pattern.
`Colo*r ad*s are great?`

matches both "Color ads are great!" and
"Colour adverts are great?".

Sometimes, the special characters `?`

and `*`

need to
be included as-is, and not have special meaning. To achieve that, precede
each special character with a tilde ("~"). This formula returns 100, because
only "ca*" in the first array matches "ca~*":

In other words, to search for a verbatim "*" character, write "~*". To search for a verbatim "?" character, write "~?". Finally, to search for a verbatim "~" character, write "~~".

Regular expressions are much more powerful than wildcards, but are also harder to learn. Use REGEXMATCH in a formula fragment to use regular expressions with PRODUCTIF.

This formula returns 10, because only "123" matches the "\d\d\d" regular expression (which only accepts exactly three numbers):

Refer to the documentation of REGEXMATCH for more information.

## Using FILTER, PRODUCT and REDUCE instead of PRODUCTIF

The work PRODUCTIF performs can be divided into two parts: filtering out the unwanted elements from the first array and then returning the product of the elements of the second array which correspond to the ones in the first array which made the cut. As such, these formulas are equivalent:

The version using FILTER provides more flexibility, as the final step (multiplying the remaining numbers together through PRODUCT) can be replaced by another function (like SUM, which adds the remaining numbers together), independent of the filtering logic.

For even more flexibility, consider replacing PRODUCT with REDUCE. Refer to the example below for more information.

## Examples

Returns 2,000 (40 * 5040 * 50). It does not return 60,000 (30 * 40 * 5030 * 40 * 50), as only 4 and 5 of the first array are greater than 3, as specified by the ">3"">3" parameter.

Returns the product of the elements of the { 11, 22, 33, 44, 55 }{ 11; 22; 33; 44; 55 } array, where only elements corresponding to elements in the Field1:Field5Field1:Field5 range greater than 1 are considered.

Returns 1,200 (30 * 4030 * 40), as only 30 and 40 correspond to "Eve" in the first array.

Determines which of the numbers of the { 3, 4, 5 }{ 3; 4; 5 } array are
greater than the value of *Field1* and returns the product of the
corresponding numbers of the { 30, 40, 50 }{ 30; 40; 50 } array. If
Field1.ValueField1,Value is 3,
then 2,000 is returned (40
* 5040 * 50).

Returns 40, which corresponds to 4 in the array { 3, 4, 5 }{ 3; 4; 5 }, which is the only element of that array which satisfies the "=4""=4" condition.

Returns 40, which corresponds to 4 in the array { 3, 4, 5 }{ 3; 4; 5 }, which is the only element of that array which satisfies the "4""4" condition. Equality is implied when no operator is specified.

Returns 40, which corresponds to 4 in the array { 3, 4, 5 }{ 3; 4; 5 }, which is the only element of that array which satisfies the 44 condition. When a plain value is given as the second parameter, equality is implied. If only values equal to another value should be considered, using a plain value instead of a value enclosed in a text string is preferable, as errors can be detected at an earlier stage.

Returns 156 (12 * 1312 * 13). It does not return 1,716 (11 * 12 * 1311 * 12 * 13), as only 1.2 and 1.3 are greater than 1.1, as specified by the ">1.1"">1.1" parameter. A decimal separator appropriate for the language of the app should be used. If that language is French, for instance, a decimal comma should be used ("1,1" instead of "1.1").

Returns 1,000 (10 *
10010 *
100), because both "car" and "cat" match the wildcard "ca?".
`?`

matches exactly one (arbitrary) character.

Returns 1,000 (10 *
10010 *
100), because both "car" and "card" match the wildcard "ca*".
`*`

matches zero, one or several (arbitrary) characters.

Returns 100, because only "ca*" matches the wildcard "ca~*".
`~`

written before any other character matches it verbatim,
meaning that `*`

does not have special meaning here.

Returns 2,000 (40 *
5040 *
50). It does not return 60,000 (30 * 40 * 5030 * 40 * 50), as only 4
and 5 of the first array are greater than 3, as specified by the
Element >
3Element >
3 parameter. This example uses a formula fragment instead of a
text string. It is invoked once for every array element considered, and
is expected to return TRUE if the value should be considered and FALSE
otherwise. To do its work, it has access to the `Element`

value, which is the value under consideration.

Returns 50, as only 5 in the first array is both greater than 3 and is an odd number and it corresponds to 50 in the second array. Formula fragments have access to the full power of the formula language, enabling complex conditions. && means logical "and," || means logical "or" and ! means logical negation.

Returns 2,000 (40 *
5040 *
50). It does not return 60,000 (30 * 40 * 5030 * 40 * 50), as only 4
and 5 in the second array are greater than 3. FILTER returns a version of the first
array, where elements corresponding to elements of the second array which
failed the `> 3`

test are not present. Finally, PRODUCT multiplies the
remaining numbers (40 and 50) together and returns the result.

Returns 2,000 (40 * 5040 * 50). It does not return 60,000 (30 * 40 * 5030 * 40 * 50), as only 4 and 5 of the second array are greater than 3. REDUCE is a more flexible alternative to functions like PRODUCT. Refer to its documentation to learn how it works.

Returns 10, because only "123" matches the "\d\d\d" regular expression (which only accepts exactly three numbers). Refer to the documentation of REGEXMATCH for more information.