Forum Discussion
How to return counts of text cells across multiple worksheets in same workbook?
=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.
Oh wow! This will be sweet if it works. I will let you know how it goes. Thank you!!
Unfortunately, I'm restricted by admin. from using macros due to security concerns. Apparently, that is what this is because I get warnings left and right when I try and paste into my workbook. Also, when I just type =GROUPBY, it's not listed as active. Back to the drawing board!
If it would be helpful, as suggested in the first response above, I could send something with 'dummy' data so you can see what I have structurally.
The formula i suggested isn't a macro but a dynamic arrayformula. However GROUPBY isn't already available for all Microsoft 365 users unfortunately. I assume that you work with english Excel otherwise you'd have to translate GROUPBY, for example in german Excel it's GRUPPIERENNACH.
If you could send a file or a screenshot that shows your 'dummy' data without sensitive content i'd try to suggest a suitable solution.
Here is what I have for you to work with. Some of the drop menus don't work, but I did the best I could and still show what I'm doing. I included 9 offices and put some dummy data in there for each. Hopefully, this will suffice. Thank you!