Pivot tables are good for everyone – they calculate quickly, and are configured flexibly, and the design can be wound into them elegantly, if required. But there are also a few fly in the ointment, in particular, the inability to create a summary, where the value area should not contain numbers, but text.
Let’s try to get around this limitation and come up with a “couple of crutches” in a similar situation.
Suppose our company transports its products in containers to several cities in Our Country and Kazakhstan. Containers are sent no more than once a month. Each container has an alphanumeric number. As initial data, there is a standard table listing deliveries, from which you need to make some kind of summary in order to clearly see the numbers of containers sent to each city and each month:
For convenience, let’s make the table with the initial data “smart” in advance using the command Home – Format as a table (Home — Format as Table) and give her a name Deliveries tab Constructor (Design). In the future, this will simplify life, because. it will be possible to use the name of the table and its columns directly in the formulas.
Method 1. The easiest – use Power Query
Power Query is a super powerful tool for loading and transforming data in Excel. This add-in has been built into Excel by default since 2016. If you have Excel 2010 or 2013, you can download and install it separately (completely free).
The whole process, for clarity, I analyzed step by step in the following video:
If it is not possible to use Power Query, then you can go other ways – through a pivot table or formulas.
Method 2. Auxiliary summary
Let’s add one more column to our original table, where using a simple formula we calculate the number of each row in the table:
Obviously, -1 is needed, because we have a one-line header in our table. If your table is not at the beginning of the sheet, then you can use a slightly more complex, but universal formula that calculates the difference in the numbers of the current row and the table header:
Now, in a standard way, we will build a pivot table of the desired type based on our data, but in the value field we will drop the field Line number instead of what we want container:
Since we do not have several containers in the same city in the same month, our summary will, in fact, give out not the amount, but the line numbers of the containers we need.
Additionally, you can turn off grand and subtotals on the tab Constructor – General totals и Subtotals (Design — Grand Totals, Subtotals) and in the same place switch the summary to a more convenient table layout with the button Report mockup (Report Layout).
Thus, we are already halfway to the result: we have a table where, at the intersection of the city and month, there is a row number in the source table, where the container code we need lies.
Now let’s copy the summary (to the same sheet or another) and paste it as values, and then enter our formula into the value area, which will extract the container code by the line number found in the summary:
Function IF (IF), in this case, checks that the next cell in the summary is not empty. If empty, then output an empty text string “”, i.e. leave the cell blank. If not empty, then extract from the column Container source table Deliveries cell content by row number using function INDEX (INDEX).
Perhaps the only not very obvious point here is the double word Container in the formula. Such a strange form of writing:
Supplies[[Container]:[Container]]
… is only needed to reference the column Container was absolute (like a reference with $ signs for ordinary “non-smart” tables) and did not slip to neighboring columns when copying our formula to the right.
In the future, when changing the data in the source table Deliveries, we must remember to update our auxiliary summary with line numbers by right-clicking on it and choosing the command Update & Save (Refresh).
Method 3. Formulas
This method does not require the creation of an intermediate pivot table and manual updating, but uses Excel’s “heavy weapon” – the function SUMMESLIMN (SUMIFS). Instead of looking up row numbers in a summary, you can calculate them using this formula:
With some external bulkiness, in fact, this is a standard use case for the selective summation function SUMMESLIMNA that sums the row numbers for the given city and month. Again, since we do not have several containers in the same city in the same month, our function will, in fact, give out not the amount, but the line number itself. And then the function already familiar from the previous method INDEX You can also extract container codes:
Of course, in this case, you no longer need to think about updating the summary, but on large tables, the function SUMMESLI can be noticeably slow. Then you will have to turn off automatic updating of formulas, or use the first method – a pivot table.
If the appearance of the summary is not very suitable for your report, then you can extract row numbers from it into the final table not directly, as we did, but using the function GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). How to do this can be found here.
- How to create a report using a pivot table
- How to set up calculations in pivot tables
- Selective counting with SUMIFS, COUNTIFS, etc.