To obtain a distinct count in an Excel 2013 PivotTable, here is what we do. We expect 4 for S, since there are four items (X101, X102, X103, X104). We expect 2 for N, since there are two items for N (X101, X102). We want a PivotTable that reports 1 for E since there is one unique region/item combination for E (X101). We do not want to count the number of transactions/rows, we want to count the unique, or distinct, number of region/item combinations. The count illustrated above is not what we want. This is illustrated in the screenshot below. With Excel 2010 and earlier, the best we could do is to count the number of transactions (rows) with any given region/item combination. For example, we want the PivotTable to show that for region N, the count of unique items is 2. Our objective is to create a PivotTable that counts the number of unique region/item combinations. We have some sales transactions with region, item, and amount columns, as shown below. Objectiveįirst, let’s review our objective. This post demonstrates how to perform a distinct count with an Excel 2013 PivotTable. The good news is that beginning with Excel 2013, this capability is built-in to the standard PivotTable feature. For Excel versions earlier than 2013, there are a variety of different workarounds available, some use VBA code, some use helper formulas, and some of them use functions such as COUNTIF, COUNTIFS, and SUMPRODUCT. Prior to Excel 2013, this capability was not built-in to the PivotTable feature. This post demonstrates how to count the number of distinct (unique) values in an Excel 2013 PivotTable.
0 Comments
Leave a Reply. |