- Okay, if you're using Google sheets and you have
a table of data, like the one that we have here,
you may want to find out how many
duplicates are in it, or you may wanna find out
how many unique values are in it.
We're going to go over a way to do each one of those.
And so we'll start right now.
(electronic music begins)
To count the number of duplicate values,
the way that we're going to do it is we're going to go
through each row and put a number here that indicates
how many times that value has occurred so far.
And then we'll do some magic at the bottom to add it up.
All right so the formula that we're going to do is
a Count F, so I'll type in the Count F and the magic
kind of happens in how we put in the range here.
So the range right now, we just want to look at B6, okay,
because it's only occurred, the value 8
has only occurred once.
As we go down, we're going to expand the range.
So let's just leave the range of B and we're going to
fix the 6 so it doesn't move, to B6, but we want
that one to go down, so we'll leave it.
And we just want to count on if it's the same as B6.
So make more sense as we copy it down.
So right now, it's giving you a 1 because it's seen one 8.
Alright, so let's copy this formula down.
And it starts to give a value more than one
once it sees a duplicate.
So let's double-click in cell B9
and see how this is returning a 2.
Since we fixed the top of this range,
B6 to B9, but we didn't fix the bottom,
it's expanded down far enough where it's showing two 3s.
So when it's looking to see this value in B9,
it's counting it twice.
So what we end up with here is a column of values
where every time it's greater than one,
it means there's a duplicate.
Okay so if we wanted to count the duplicates,
let's first fix the formatting here.
When I copied that formula down,
I think it copied the formatting, too,
so it took away my double underline.
So in case you're wondering what I did there,
I just went to a cell where I liked the formatting
and wanted it, and I left-clicked on Paint format,
and then I left-clicked where I wanted it.
It's a nice little shortcut.
I copied that Count F formula down one too many,
So let's delete that.
Actually, that might've been right formula,
but I just left it in there, I need to explain it.
All right, so what we wanna do, we wanna count
every occurrence in that column
where the value is greater than one.
So we'll do Count F again, and this one'll be a bit more
simple, we know the range, it's not going to move around.
So we'll select C6 to C14 and the criteria is just that.
And you have to put this in quotes.
I don't think it seems like you should,
but you just should, greater than one,
close it off and hit enter.
So it's saying there's four duplicates here.
So let's check that real quick, we have 8, 7, 3.
So this next 3 is a duplicate.
So is the 8, and this 3 and this 3.
So that's right, there's four duplicates.
So now we've counted the duplicates,
let's move on to the unique values.
I'll delete this here, so don't give away the secret.
Counting the unique values is a whole lot easier
because there's a built-in function
in Google Sheets called Count unique.
And this function's not in Excel, so in Excel,
you would have to build a formula,
but since Google Sheets is so awesome,
it's going to do it for you, Count unique,
and all it needs is a range.
So we'll give it the range, close it off, hit enter.
And there you go, there's five unique values.
So not coincidentally, there's nine values here.
If you add the five and the four,
then you get to nine, right?
So there's five of these values are unique,
five of them are duplicates.
All right so our next video is going to be
how to sort and filter by color.
So if you have a spreadsheet where yellow highlighting
means something, and the ones in red mean something,
you'll be able to sort based on those criteria
with the built-in functions.
So I'll see you in that next video, thanks.