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+] |
