Distinct count of merged cells not working in pivot table

[faeriedust66]

Hello!

I have an Excel spreadsheet with a table that displays (A) Items, (B) # of Items that are past due, and (C) Owners assigned to the Items. Some Items are assigned to multiple Owners.

For example, in my sample worksheet, Item #5 is assigned to both DP and DJ.

I created a Pivot Table that displays the distinct count of Items for each Owner and how many of the Items for each Owner are past due.

My pivot table displays correctly up to this point - 81 distinct Items, and a count of 54 items that are past due.

To have my Source Data visually display that a certain Item # is assigned to multiple individuals, I merged the cells that have the same Item # (Source Data - merged cells tab on my sample worksheet).

After the cells have been merged and I update my Pivot table, the count of distinct Items goes up by 1 (from 81 to 82) and the distinct count for DJ goes down by 7 (from 31 to 24).

Can anyone tell me why this is happening? And why only by 1 and not 10, since I merged the cells for 10 Items? Does merging cells interfere with the pivot table distinct count?

Thank you for your help!

[Pepe Le Mokko]

https://excel.solutions/2016/10/usin...ctively-excel/