Forum Discussion
Fast calculation
Hi, I need some help to make my life easier..
My document contains about 100 sheets of clothes size information.
Each sheet contains the clothes-sizes of an employee, name of the sheet is the name of the employee. Each sheet looks the same, so every T-shirt size is put in the same cel.
I would like to calculate how many of the employees have size M (for example).
I'm pretty sure this isn't a difficult question, but I can't seem to find the solution..
Thanks!
There's a little known feature in Excel that is hard to explain,
where you can get a range of ranges by sheet (see my example it's just easier). This is easier to show in the attached spreadsheet (no macros). As you can see in the screen shot below, I have two sheets with green coloring called firstSheet and LastSheet, with several yellow worksheets in between.
What I can do from the CalcSheet (think summary or wherever you want to analyze), you can then conduct calculations on all these worksheets' same cell. In your case, you said all t-shirt sizes are in the same cell. For the sake of discussion let's assume it's cell C1.
I can then use ToCol to stack these and basically do whatever you want after this.=LET(fullList,TOCOL(firstSheet:LastSheet!C1),fullList)Thus in your particular case, if you were to just insert a firstsheet and lastsheet between your 100 worksheets (similar to the yellows...) and leverage formula like the following, but just updating C1 to be whatever cell has the keyvalue, you can then sum the result.
=LET(fullList,TOCOL(firstSheet:LastSheet!C1),mediumOnly,FILTER(fullList,fullList="M",0),ROWS(mediumOnly))Here's a visual which might help if you can't
see file
You have at least two options:
- Consolidate sheets with VSTACK and summarize with GROUPBY or PIVOTBY
- Consolidate sheets with PowerQuery then summarize with a pivot table
Without seeing the arrangement of the data or a sample it's difficult to go into more depth.
