FOMULAS
Formula Input data Goal of function Notes
COUNT =COUNT(value1,[value2],[…]) It returns the number of • Maximum of 255 inputs
cells in a range that • Cells that contain dates and times are counted
contain numeric values • Cells that are empty or contain text, error messages
or logical values (TRUE and FALSE) are excluded
COUNTA =COUNTA(value1,[value2],[…]) It returns the number of • Maximum of 255 inputs
cells in a range that • It considers all cells numbers and text strings, with
contain data, i.e. which error values, empty values (only if formula that
are not empty returns empty value "") and logical operators TRUE
and FALSE
COUNTBLANK =COUNTBLANK(range) It returns the number of • Must be a range of contiguous cells
empty cells in a range • Also accepts =COUNTBLANK (A:A) for whole
column or =COUNTBLANK(1:5) for whole rows
• It considers cells that contain a formula that returns
an empty value ("") as a result
SUM =SUM(number1,[number2],[...]) It returns the arithmetic • Maximum of 255 inputs
sum of a set of numerical • Any blank cell or any cell containing text is not
values considered – i.e. if you select a range with numbers
and texts, it will give you the sum of the numbers
ignoring the text strings (no error message)
• If none of the arguments refers to numeric values, the
function returns 0
AVERAGE =AVERAGE(number1,[number2],[...]) It returns the arithmetic • Maximum of 255 inputs
mean of a set of • Any blank cell or any cell containing text is not
numerical values considered – i.e. if you select a range with numbers
and texts, it will give you the average of the numbers
ignoring the text strings (no error message)
• A cell with ‘0’ is considered
• If none of the arguments refers to numerical values,
it returns the error #DIV/0!
MAX =MAX(number1,[number2],[...]) It returns the largest • Maximum of 255 inputs
value of a numeric set • Any blank cell or cell containing text is not
considered
• If none of the arguments refers to numeric values, the
function returns 0
, MIN =MIN(number1,[number2],[...]) It returns the smallest • Maximum of 255 inputs
value of a numeric set • Any blank cell or cell containing text is not
considered
• If none of the arguments refers to numeric values, the
function returns 0
IF =IF(logical_test,[value_if_true],[value_if_false]) It performs conditional • Formatting of logical tests: A2>=10, B2="Milan",
tests on values and K32<>"Full" – inequalities don’t require quotation
formulas marks
• If the second and third input are left blank, the
function will return TRUE or FALSE
• Text strings for the second and third input must be
written in quotation marks
TRIM =TRIM(text) Removes all spaces from
the text, leaving only
spaces between
individual words in a text
string
AND =AND(logical1,[logical2],[...]) It returns TRUE if all the • TRUE when all arguments are true
conditions specified in • Maximum of 255 arguments
the arguments are true
OR =OR(logical1,[logical2],[...]) It returns TRUE if at • TRUE when at least one of the arguments is true
least one of the • Maximum of 255 arguments
conditions specified in
the arguments is true
SUMPRODUCT =SUMPRODUCT(array1,array2,[array3],[...]) It returns the sum of the • Inputs: from a minimum of 2 to a maximum of 255
products of two or more • Each input is an array (that is a range of cells)
arrays of equal size • Arrays can be of any size, but they must be equal!
• If arrays have different dimensions the function
returns the error #VALUE!
• Any non-numeric element in an array is treated as a
zero
SUMIF =SUMIF(range,criteria,[sum_range]) It returns the sum of cells • The input ‘range’ contains the reference to the range
in a range that meet a of cells that we want to be evaluated by the criteria
certain criterion • The cells in the range argument can contain numbers,
text strings, formulas, functions, expressions, or
references to cells that contain numbers or text
strings. Empty cells are ignored.
• The criteria input must always be enclosed in
quotation marks – both textual and logical or
mathematical criteria
• ( "> 500", "Italy", "<=60", "<>Milan",
">11/05/2020")
Formula Input data Goal of function Notes
COUNT =COUNT(value1,[value2],[…]) It returns the number of • Maximum of 255 inputs
cells in a range that • Cells that contain dates and times are counted
contain numeric values • Cells that are empty or contain text, error messages
or logical values (TRUE and FALSE) are excluded
COUNTA =COUNTA(value1,[value2],[…]) It returns the number of • Maximum of 255 inputs
cells in a range that • It considers all cells numbers and text strings, with
contain data, i.e. which error values, empty values (only if formula that
are not empty returns empty value "") and logical operators TRUE
and FALSE
COUNTBLANK =COUNTBLANK(range) It returns the number of • Must be a range of contiguous cells
empty cells in a range • Also accepts =COUNTBLANK (A:A) for whole
column or =COUNTBLANK(1:5) for whole rows
• It considers cells that contain a formula that returns
an empty value ("") as a result
SUM =SUM(number1,[number2],[...]) It returns the arithmetic • Maximum of 255 inputs
sum of a set of numerical • Any blank cell or any cell containing text is not
values considered – i.e. if you select a range with numbers
and texts, it will give you the sum of the numbers
ignoring the text strings (no error message)
• If none of the arguments refers to numeric values, the
function returns 0
AVERAGE =AVERAGE(number1,[number2],[...]) It returns the arithmetic • Maximum of 255 inputs
mean of a set of • Any blank cell or any cell containing text is not
numerical values considered – i.e. if you select a range with numbers
and texts, it will give you the average of the numbers
ignoring the text strings (no error message)
• A cell with ‘0’ is considered
• If none of the arguments refers to numerical values,
it returns the error #DIV/0!
MAX =MAX(number1,[number2],[...]) It returns the largest • Maximum of 255 inputs
value of a numeric set • Any blank cell or cell containing text is not
considered
• If none of the arguments refers to numeric values, the
function returns 0
, MIN =MIN(number1,[number2],[...]) It returns the smallest • Maximum of 255 inputs
value of a numeric set • Any blank cell or cell containing text is not
considered
• If none of the arguments refers to numeric values, the
function returns 0
IF =IF(logical_test,[value_if_true],[value_if_false]) It performs conditional • Formatting of logical tests: A2>=10, B2="Milan",
tests on values and K32<>"Full" – inequalities don’t require quotation
formulas marks
• If the second and third input are left blank, the
function will return TRUE or FALSE
• Text strings for the second and third input must be
written in quotation marks
TRIM =TRIM(text) Removes all spaces from
the text, leaving only
spaces between
individual words in a text
string
AND =AND(logical1,[logical2],[...]) It returns TRUE if all the • TRUE when all arguments are true
conditions specified in • Maximum of 255 arguments
the arguments are true
OR =OR(logical1,[logical2],[...]) It returns TRUE if at • TRUE when at least one of the arguments is true
least one of the • Maximum of 255 arguments
conditions specified in
the arguments is true
SUMPRODUCT =SUMPRODUCT(array1,array2,[array3],[...]) It returns the sum of the • Inputs: from a minimum of 2 to a maximum of 255
products of two or more • Each input is an array (that is a range of cells)
arrays of equal size • Arrays can be of any size, but they must be equal!
• If arrays have different dimensions the function
returns the error #VALUE!
• Any non-numeric element in an array is treated as a
zero
SUMIF =SUMIF(range,criteria,[sum_range]) It returns the sum of cells • The input ‘range’ contains the reference to the range
in a range that meet a of cells that we want to be evaluated by the criteria
certain criterion • The cells in the range argument can contain numbers,
text strings, formulas, functions, expressions, or
references to cells that contain numbers or text
strings. Empty cells are ignored.
• The criteria input must always be enclosed in
quotation marks – both textual and logical or
mathematical criteria
• ( "> 500", "Italy", "<=60", "<>Milan",
">11/05/2020")
