Pivot Issue with Sums

Hi there folks,

I'm looking to ask for your help on an issue I'm having with a pivot table.

The situation is the following:

I have data on 1 group of users, who use Chrome, and another list of users who use Paint.
I'm trying to calculate the number of users who use both chrome and paint.

The way I've got it set up is: 1 worksheet each for the user lists. On the worksheet for chrome, I have a "paint" column, where I'm using a countif formula to get a binary value (1 for match, 0 for no match) between the 2 applications. Formula:
=COUNTIF('Paint users'!$F:$F,'Chrome users'!$A:$A)

However, the sum of matches exceeds the number of users for paint, which doesn't make sense...
Refreshing the pivot gives me a lower value, which changes back to the original wrong value upon another refresh.

I'm at a loss here. I've cleansed the data several times (though I've had issues before, as I had filtered an original longer user list based on a certain criteria. This resulted in some values getting hidden, but still showing up in the calculations.)

What am I doing wrong?
Please let me know if I need to clarify anything.

Thanks in advance!

Originally Posted by JosephP

it sounds like you have duplicated user names

Hi there,

I filtered out duplicated usernames by: selecting the column, data-advanced-unique records only.
I then selected the filtered list, and used go to (special) - select visible cells only.

The resulting list does not seem to have any duplicated usernames at this point. As far as I can tell, the invisible duplications were removed with the selection & copying over of visible cells only.

Originally Posted by JosephP

Does a simple sum of the source data formula column produce the correct total? I reckon it probably won't.

Problem is I don't know the correct answer, so I can't compare.

The sum of all matches exceeds the number of users on the shorter list though.

Originally Posted by JosephP

If you use MAX on your formula column is the result higher than 1? If it is, you have duplicates in the shorter list; if not, you must have duplicates in the longer list.

Max value is 1 on the formula column.
Double-checked the longer list, but there don't seem to be any duplications (did a count, also filtered for duplicates again).

Not sure how to proceed. Do you have any further ideas?

I found the issue:

Turns out I have to select visible only - copy - paste values only

This way I'm not copying over the filtered-out duplicates in hidden lines.
It was indeed a problem with the shorter list. Thanks for your help Joseph, much appreciated!