## Functions Whose Arguments Accept References Spanning Worksheets (Ready Reference 3.0)

There are a set of aggregation and summarisation functions in Excel that can usefully accept multi-sheet range references, vastly simplifying the process of summarising data across multi-sheet workbooks.

Not all functions in Excel, however, support range references that span multiple worksheets.  Whilst they may support different arguments deriving their values from ranges from different worksheets, for most functions, a range that spans multiple sheets is not valid in a single argument.  The list below includes those functions whose arguments may contain a reference to a range that spans multiple sheets.

For each of the following functions, the up to 255 arguments they support may be references to a range that spans multiple worksheets,
for example: =SUM(Start:End!B7:B20)

###### Logical Functions
AND(logical1,[logical2],...)
OR(logical1,[logical2],...)
XOR(logical1,[logical2],...)  [2013+]

###### Text Functions
CONCAT(text1,[text2],...)  [2013]
TEXTJOIN(delimiter,ignore_empty,text1,[text2],...)  [2016]

###### Mathematical & Trigonometrical Functions
PRODUCT(number1,[number2],...)
SUM(number1,[number2],...)
SUMSQ(number1,[number2],...)

###### Statistical Functions
AVEDEV(number1,[number2],...)SKEW(number1,[number2],...) STDEVA(number1,[number2],...)
AVERAGE(number1,[number2],...) STDEV.P(number1,[number2],...)  [2010]
AVERAGEA(number1,[number2],...) STDEVP(number1,[number2],...)  [deprecated 2010]
COUNT(value1,[value2],...) STDEVPA(number1,[number2],...)
COUNTA(value1,[value2],...) STDEV.S(number1,[number2],...)  [2010]
GEOMEAN(number1,[number2],...) VAR(number1,[number2],...)  [deprecated 2010]
HARMEAN(number1,[number2],...) VARA(number1,[number2],...)
KURT(number1,[number2],...) VAR.P(number1,[number2],...)  [2010]
MEDIAN(number1,[number2],...) VARP(number1,[number2],...)  [deprecated 2010]
SKEW.P(number1,[number2],...)  [2013] VARPA(number1,[number2],...)
STDEV(number1,[number2],...)  [deprecated 2010] VAR.S(number1,[number2],...)  [2010+]

Clarkson ITT builds & delivers
expert training, in classrooms
& online, for professionals in
finance, funds management &
accounting - developing their
skills in modelling and analysis.