Slicers vs. SUMIFs

Q: Our accounting system produces a report containing a large table of inventory information, which I export to Excel, where I then want to look up values according to multiple columns of criteria. I use this data to plan my weekly orders. For example, from the following table, I might want to calculate how many large gray flannel shirts we have available to sell, or how many total T-shirts we have in stock from a specific vendor.

Pivoting the data produces the answers I seek, but the report is a mile long and difficult to navigate. It doesn’t seem necessary to produce more than 100,000 values via a PivotTable when I need only a handful of values. I’ve tried using VLOOKUP and SUMIF functions, but I can’t seem to come up with a formula-based solution that works. Can you suggest an easier approach?

A: Your PivotTable solution has merit; you just need to add a few slicers to filter the PivotTable and simplify your results. To add slicers to your example PivotTable, click anywhere on the PivotTable to select it, then from the Insert tab, select Slicer from the Filter group. In the resulting Insert Slicers dialog box, check the slicers labeled Description, Size, Color, and Vendor, and then click OK. This action will insert four slicers, as pictured below.

These slicer buttons enable you to filter and view the amount of inventory in stock, committed, on order, or by vendor for any one item (or group of items if you select multiple criteria in each category). You can download an example of this solution at carltoncollins.com/slice.xlsx. While this approach works, it does require a moderate knowledge of PivotTables; therefore, keep reading for an alternative formula-based solution. (Note that I cleaned up the PivotTable pictured above by rearranging the slicers, applying PivotTable and slicer styles, removing subtotals and grand totals, and editing and formatting the PivotTable’s column headings.)

As an alternative, you might use the List command in the Data Validation dialog box and the SUMIFS function to craft a solution based on dropdown lists, as follows:

1. To create dropdown lists, start by listing the various descriptions, sizes, colors, and vendors in separate columns, as pictured below. (Hint: To create these lists, you could type the various lists manually, but if the lists are lengthy, you might want to use the Data tab’s Advanced Filter tool to copy each column’s Unique records to another location to create these lists, as pictured in the dialog box below to the right.)

2. Create the first dropdown list by selecting a blank cell (cell P5 in this example), and then from the Data tab, select Data Validation, Data Validation (yes, twice). In the resulting Data Validation dialog box, in the Allow dropdown menu, select List, and then in the Source box, click the cell chooser button and highlight the list of unique descriptions (cells W4:W7 in this example).

3. Repeat this process for the remaining three lists until your four dropdown menu options appear as pictured below.

4. To complete the solution, create a list of labels corresponding to the column data you want to display. (In this example, we want to display data from columns I, J, K, and L next to the labels entered in cells P7:P10, as pictured below). Next, use the SUMIFS function to create four formulas returning the desired results based on the dropdown selections. For example, in cell Q7, pictured below, I entered the following formula:

The SUMIFS formula syntax requires you to start by referencing the column of data to be displayed for that specific row where the subsequent pairs of cell references match.

In this example, there are four subsequent pairs or cell references, and the first pair pinpoints those cells in column B that match the T-shirt criterion listed in cell P5. Because of the multiple criteria, this results in a lengthy formula; but to express the formula’s goal using words, this formula returns the value contained in column I (the amount in stock) for that row that matches the criteria selected in the four dropdown lists. To use this workbook, select the description, size, color, and vendor from the dropdown lists to automatically display the amounts in stock, committed, on order, and available. This solution will enable you to view data for each item selected without the need to generate a massive PivotTable. You can download this solution at carltoncollins.com/ifs.xlsx.

J. Carlton Collins (carlton@asaresearch.com) is a technology consultant, CPE instructor, and a JofA contributing editor.

Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.