Forum Discussion
How to return counts of text cells across multiple worksheets in same workbook?
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!
=LET(stackedtables,VSTACK(Office1,Office2,Office3), status,HSTACK(UNIQUE(CHOOSECOLS(stackedtables,7)), BYROW(UNIQUE(CHOOSECOLS(stackedtables,7)), LAMBDA(x,ROWS(FILTER(CHOOSECOLS(stackedtables,7),CHOOSECOLS(stackedtables,7)=x))))), officeandstatus,UNIQUE(HSTACK(CHOOSECOLS(stackedtables,4),CHOOSECOLS(stackedtables,7))), nameofficestatus,UNIQUE(HSTACK(CHOOSECOLS(stackedtables,2),CHOOSECOLS(stackedtables,4),CHOOSECOLS(stackedtables,7))), IFNA( HSTACK(status,"", officeandstatus, MAP(CHOOSECOLS(officeandstatus,1),CHOOSECOLS(officeandstatus,2),LAMBDA(a,b,ROWS(FILTER(CHOOSECOLS(stackedtables,4,7),(CHOOSECOLS(stackedtables,4)=a)*(CHOOSECOLS(stackedtables,7)=b))))),"", nameofficestatus, MAP(CHOOSECOLS(nameofficestatus,1),CHOOSECOLS(nameofficestatus,2),CHOOSECOLS(nameofficestatus,3), LAMBDA(a,b,c,ROWS(FILTER(CHOOSECOLS(stackedtables,2,4,7),(CHOOSECOLS(stackedtables,2)=a)*(CHOOSECOLS(stackedtables,4)=b)*(CHOOSECOLS(stackedtables,7)=c)))))),""))This should work. Since i don't want to open files from the www i've only copied columns A - G of your first 3 tables and named them Office1, Office2 and Office3.
Yes, it's English. It's web-based access to Excel in Office 365. We have the Excel application, but mostly access this particular workbook from within either Teams or a browser window. I will try and put something together for you to see. Thanks again!
