In a number of our teaching modules, we demonstrate a technique for consolidating totals by using a SUM function to total corresponding cells through a range of worksheets.  That technique allows us to build very powerful solutions that are flexible and robust and easy to manage and maintain.  The SUM function is not the only function that can be used in this way.

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 be a reference to a range that spans multiple sheets.

For each of the following functions, each of the up to 255 arguments they support may be a reference 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],...)  [2016]
    • TEXTJOIN(delimiter,ignore_empty,text1,[text2],...)  [2016]
  • Mathematical & Trigonometric Functions
    • PRODUCT(number1,[number2],...)
    • SUM(number1,[number2],...)
    • SUMSQ(number1,[number2],...)
  • Statistical Functions
    • AVEDEV(number1,[number2],...)
    • AVERAGE(number1,[number2],...)
    • AVERAGEA(number1,[number2],...)
    • COUNT(value1,[value2],...)
    • COUNTA(value1,[value2],...)
    • GEOMEAN(number1,[number2],...)
    • HARMEAN(number1,[number2],...)
    • KURT(number1,[number2],...)
    • MEDIAN(number1,[number2],...)
    • SKEW(number1,[number2],...)
    • SKEW.P(number1,[number2],...)  [2013+]
    • STDEV(number1,[number2],...)  [deprecated 2010]
    • STDEVP(number1,[number2],...)  [deprecated 2010]
    • STDEV.P(number1,[number2],...)  [2010+]
    • STDEV.S(number1,[number2],...)  [2010+]
    • STDEVA(number1,[number2],...)
    • STDEVPA(number1,[number2],...)
    • VAR(number1,[number2],...)  [deprecated 2010]
    • VARP(number1,[number2],...)  [deprecated 2010]
    • VAR.S(number1,[number2],...)  [2010+]
    • VAR.S(number1,[number2],...)  [2010+]
    • VARA(number1,[number2],...)
    • VARPA(number1,[number2],...)