Motivated by a recent thread in Reddit's /r/excel.

Excel's SHEETS function doesn't accept 3D references in other workbooks. If in Book2, Sheet1!A1 is 1, Sheet2!A1 is 20, and those worksheets are adjacent, in Book1 the formula =SUM([Book2]Sheet1:Sheet2!A1) returns 21, but the formula =SHEETS([Book2]Sheet1:Sheet2!A1) returns #N/A. Also, VBA can't accept 3D references.

There's an XLM work-around.

Define WBList referring to =REPLACE(TRANSPOSE(DOCUMENTS()),1,0*NOW(),""). In a blank worksheet, select A1:A20 and enter the array formula =WBList. This will produce a list of the names of open non-add-in workbooks. Move ActiveCell to B1, and define WBSheets referring to =GET.WORKBOOK(4,A1). Note: use a relative reference. Then enter the formula =WBSheets in cell B1. That will be the number of sheets in the open workbook shown in cell A1. Fill down.

Supplement further with B1 still the active cell by defining WBWSList as =REPLACE(GET.WORKBOOK(1,A1),1,LEN(A1)+2,""). That's a horizontal array of the worksheet names in the open workbook shown in cell A1. Select C1:Z1 and enter the array formula =WBWSList. That will be the list of the first 24 worksheet names in the open workbook shown in cell A1. With C1:Z1 selected, fill down. Name that entire range, column A to Z, WBInfo.

You could then use =VLOOKUP(some_workbook_name,WBInfo,2,0) to find the total number of worksheets in that other workbook.

You could use =ABS(MATCH(one_sheet_name,INDEX(WBInfo,MATCH(some_workbook_name,INDEX(WBInfo,0,1),0),0),0)-MATCH(another_sheet_name,INDEX(WBInfo,MATCH(some_workbook_name,INDEX(WBInfo,0,1),0),0),0))+1 to get the number of sheets between one_sheet_name and another_sheet_name, inclusive.

That said, since Excel doesn't provide 3D indexing into 3D references, I'm not sure I see what utility the SHEETS function is supposed to provide.