Instructor: Alright, so I actually lied.
Before we go on to the next section of the course,
I wanted to add one more hands-on demo
that I think does a really good job showcasing
how you can combine these different stats functions
to do some really interesting things.
And in the demo that we're about to walk through,
we're gonna combine the COUNTIF function,
which we just talked about,
with that strange and mysterious SUMPRODUCT function
to actually count the number of duplicate items
between two lists.
So go ahead and open up your section four workbook.
You should see this orange tab at the end,
COUNTIF and SUMPRODUCT.
If you don't,
it means you're using an older version of the file.
Go ahead and jump to your course dashboard,
download a fresh copy of that workbook,
and you should be good to go.
And what we're looking at here
are the grocery lists for two roommates, Ben and Jerry,
and both Ben and Jerry are going grocery shopping today,
and the last thing that we want is for them
to both purchase the same item for the apartment.
So this will be a great opportunity to use a cell function
to actually count any duplicate items that exist
in both Ben's list in column B and Jerry's list in column D.
Now before we go crazy
and start just typing SUMPRODUCT and COUNTIF functions
right here in cell F3,
what I like to do when I'm kind of pursuing
these more advanced techniques or formulas
is break them down into their most basic component pieces,
make sure I understand those components really, really well,
and then assemble them into the final result.
And so that's exactly what we're gonna do here.
So we're gonna start by just adding some workspace here.
I'm gonna right click, insert a new column,
and just stretch out E and F.
This is gonna be kind of our sandbox or working space,
which we'll end up deleting before we finish.
And the second thing that I'm gonna do
just as we get prepped,
is instead of referencing these lists as cell references,
like B3 through B25,
let's give them some meaningful names.
That's gonna help simplify things
and make our formulas more readable.
So using that name box right to the left of the formula bar,
we're gonna just type Ben,
'cause I've got Ben's list selected, and press enter.
Same thing with Jerry.
Gonna select cells D3 through D25.
We'll call that one Jerry.
Alright, now there are two ways we can solve this problem.
We can either figure out the number of duplicates
in Ben's list that exist in Jerry's,
or we can figure out the duplicates
that exist in Jerry's list from Ben's,
and it's kind of two sides of the same coin.
We're gonna get the same results,
but we can use two variations of the formula to do that.
So for the sake of example, let's walk through both.
I'm just gonna add some headers here.
First, we're gonna say duplicates in Ben's list,
and then copy and paste that header,
and change the Ben to Jerry.
Duplicates in Jerry's list,
and to calculate these duplicates,
we're gonna use just the COUNTIF function.
Pretty straightforward.
We're gonna say equals COUNTIF,
and we need a range and a criteria,
so in this case, duplicates that exist within Ben's list.
We're basically searching Jerry's list,
and I can either drag and select
and it will update to our named range Jerry,
or I can simply start typing Jerry,
and I'll see it's populating there in my IntelliSense box.
I can tab it in.
So I'm searching within Jerry's list,
and the criteria is the first item in Ben's list.
It feels a little bit backwards,
but you'll see what I mean
when we close this out and press enter,
drag this formula down.
What's going on here
is that Excel is checking each item in Ben's list,
it's comparing it against the entire range
containing Jerry's list,
and it's returning a count of the number of instances.
So if that item, Washington cranberry juice,
does not exist in Jerry's list, we'll see a zero.
Any item that does exist, we'll see a one.
So at first glance,
this tells us that we've got some duplicates.
Green Ribbon canned peaches also appears in Jerry's list.
Blue Label fancy canned oysters
also appears in Jerry's list,
and it looks like one other duplicate
which is Club string cheese.
Okay, so by simply selecting that range,
you might start to realize that we can simply take the sum,
which is the three,
and that does give us the answer that we need,
the number of duplicate items, there are three,
and that would be just fine.
That gets us what we need,
but we're gonna get a little bit more elegant here,
and the first thing that we're gonna do
is actually change this reference of B3
to our named range that we called Ben.
And by doing this, even though we're referencing
that full grocery list,
each time when we drag this function down,
we'll get the same exact array of ones and zeros,
even though we're not referencing B3, B4, B5, B6, et cetera.
So we're able to simplify things a bit
by simply referencing each of the two named ranges.
And now very similar process.
If we wanna flip the coin here
and count duplicates in Jerry's list.
In fact, we can copy and paste that function from E3,
and all we're gonna do is reverse the order here.
And now we're checking within Ben's list
each item that exists in Jerry's.
Press enter, drag it down,
and you'll notice two interesting things here.
First is that you still get this array of ones and zeros,
but the ones are located on different rows.
Club string cheese, Green Ribbon canned peaches,
and those fancy canned oysters.
So it's the same three products.
Gives us that same sum of three,
but it's calling out the rows
in the location that they exist in Jerry's list
versus the location that they exist in Ben's list.
So regardless of which list we use as our source list
or our first argument in the COUNTIF function,
we're gonna get the correct answer.
Now all we need to do is basically sum up the results
of that COUNTIF function
to boil it down into our single answer,
which in this case we know will be three products,
and that's where SUMPRODUCT comes into play.
So right here in G3, let's start with a SUMPRODUCT,
and now instead of pointing to specific arrays,
we're gonna embed that same COUNTIF function,
either one that we wrote in columns E or F.
So start with Ben or start with Jerry, doesn't matter.
Close off two parentheses, and that should do the trick.
So essentially within this SUMPRODUCT function,
we're embedding that whole matrix of values
and then SUMPRODUCT is adding all those values up
within a one single cell.
So press enter, boom, there you go.
We have our answer of three products, and check it out.
Instead of writing 22 functions here in 22 cells,
we've achieved the same thing using one single function,
and that's the power of SUMPRODUCT.
So with that, we actually don't need these cells anymore.
We can delete those,
and we still have our duplicate item count of three.
So there you have it.
That's kind of your crash course
in how you can combine COUNTIF and SUMPRODUCT
to count duplicates.
Hopefully this gets your gears turning
to figure out some more interesting ways
to combine these types of functions.