How to not count duplicate values in pivot table
Count Unique Values in PivotTables in Excel
Timecodes:
hello and welcome to this video tutorial
from computerguard.com and in this video
we are going to look at how to count the
unique
occurrences of a value in our pivot
table
so what we'd like to achieve is we want
to be able to do
things like count how many different
products we have sold by a country
or see how many different customers we
have by
country and these kind of actions so you
want the unique
occurrences of each value
so i've got an example pivot table on my
other sheet at the moment
which is not working to plan when you
just do a
a normal count on a value and i've got a
count
on the customer name here by a country
so i was hoping that would show me how
many individual
customers by country but it's not doing
it because it's counting
every single transaction
so on the other sheet here it's seen
that they are
three different france transactions
but i want that showing as one because
they're all the same customer
so there's one customer there's another
one customer
so each of those equal two customers in
france
and so on a unique count
or in excel 2013 what is known as a
distinct
count so let's look at how to do that
so i've got my list of orders here i
will create a pivot table
in an unusual way insert pivot table
but with the exception that i need to
check
the add this data to data model
checkbox now this is a new feature to
excel 2013.
it cannot be achieved in any version
prior to 2013 not without something
known as the power pivot
anyway so you need to have 2013 here and
we'll
check this box and click ok and that
will create a pivot table
that won't look any different to any
other pivot table that you've used
before
but what it does mean is that on the
right hand side i can
select country and put that into my rows
area
and then i can move customer name into
my values
area to perform a count
just like the example we just saw which
we do not want but we can now convert
this
to a distinct count and we can do that
by
opening the value field settings dialog
i can do that
among many ways by right clicking on one
of the values and choosing value field
settings
and from here scrolling right to the
bottom of the list of functions
there is a new one called distinct
count and if i select that and click ok
i now have a unique count for each
customer
by a country so i can now see that i
have three
customers in argentina and france i was
given example with this 10
different customers in there so i have a
unique account
so that is unique count pivot tables
excel 2013
please check out some of the other tips
and tutorials
at computerguard.com
Related queries:
pivot table count without duplicates
pivot table counting duplicate values
how to not count duplicates in pivot table
pivot table duplicate values
