Excel 2016: Pivot table counting each instance of PO Numbers instead of unique PO Numbers

Question

Hello All,

I am creating a pivot table that ages a worksheet of POs and dollar values by type (opex, capex). I did select "Add this date to the date model" option. The dollar values are aged correctly by the pivot table. However, I am not able to get distinct counts of the POs by selecting POs and moving it into the Values section of the pivot table.

If I move the POs into the Rows portion of the pivot table, each PO shows up only once. How can I get a unique count of the POs to occur?

Answer 1

Hi,

If you want to count the unique values of the POs, you'll have to use a workaround, as the default "Values" area doesn't provide a distinct count option.

Here's how you can do it-

1. Keep the "POs" field in the "Rows" section as you mentioned, that gives you a unique list of POs.
2. Assuming your POs are in column A of your original data table (before creating the pivot table), add a new column (helper column) with the formula =1/COUNTIF(A$2:A2, A2). Each PO is given a reciprocal value according to this formula, which is higher for the first occurrence and lower for subsequent occurrences. You will later be able to achieve distinct count thanks to this.
3. Using the original data range, including the helper column, create your pivot table now. As before, put the POs in the "Rows" section.
4. Add the helper column to the PivotTable's "Values" section. Change the helper column's aggregation function from SUM (the default) to AVERAGE. This essentially gives you the distinct count of POs.
5. Due to the helper column, your PivotTable might display subtotals. By selecting "Field Settings" from the context menu when you right-click the field in the "Rows" section, you can delete them. Select "None" under "Subtotals & Filters" for "Subtotals".

Best Regards.