How to return counts of text cells across multiple worksheets in same workbook?

Suggested by Patrick2788  Power Query could be simplified a bit, actually we may have only one query

let
    Source = Excel.CurrentWorkbook(),

    Tabs = Source{[Name="OfficeTabs"]}[Content][Office Tabs List],
    Tables = List.Transform( Tabs, (q) => q & "Tbl"),

    SelectTables = Table.SelectRows(Source, each List.Contains( Tables ,[Name] ) ),
    SelectContent = Table.SelectColumns(SelectTables,{"Content"}),

    NamesToKeep = {"Office", "Entered By", "Status:"},
    ExpandContent = Table.ExpandTableColumn(SelectContent, "Content", NamesToKeep)

in
    ExpandContent

loaded into the grid as PivotTable. If transformation of each table is required and/or it'll be ranges, not tables - above works as idea, changes won't be dramatically complex.

See CombineOffices query in attached.

Without access to GROUPBY and PIVOTBY that can make short work of this task, your best bet may be to load the tables into PowerQuery, combine, and create pivots:

I typically prefer using formulas for a task like this but the summarization can get a bit messy without a full complement of functions available.

I've attached the workbook with the pivots.

Power Query documentation - Power Query | Microsoft Learn

Can you please post few sample data and show your desired output? You may attach a sample file here or share via OneDrive, Google-Drive, Dropbox or same.

=IFNA(LET(StackedSheets,VSTACK(Office1:Office3!A2:C10),
HSTACK(
VSTACK(HSTACK("Status","Count"),GROUPBY(CHOOSECOLS(StackedSheets,3),CHOOSECOLS(StackedSheets,3),ROWS,,0)),"",
VSTACK(HSTACK("Office","Status","Count"),GROUPBY(CHOOSECOLS(StackedSheets,2,3),CHOOSECOLS(StackedSheets,3),ROWS,,0)),"",
VSTACK(HSTACK("Rep's name","Office","Status","Count"),GROUPBY(StackedSheets,CHOOSECOLS(StackedSheets,3),ROWS,,0)))),"")

If you have access to GROUPBY you can apply this formula. In the first step all tables are stacked into one combined table and then GROUPBY returns the results in a way similar to a Pivot Table. Instead of sheets Office1:Office3 you can reference sheets Office1:Office38 in the same way.

This is how the tables are laid out in my example. Above is sheet "Office3" for all entries of this office.