Sergio from Mexico asks how to count unique combinations of two fields. Episode 941 shows three methods for solving this problem.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question is from Sergio in Mexico.
Sergio says, I have two columns -- NAMES in column A, COLOR is in column B. I want to figure out how many times each name occurs with each color.
3 ways to do this.
We're going to talk about all 3 today.
First one, INSERT, PIVOT TABLE.
I want to go to an existing location.
We'll just move it out to the right-hand side here.
Click OK.
Alright.
So, we're going to put NAME down the left-hand side, COLOR going across the top, and then COLOR in the SUM VALUES, and that forces Excel to count because it's a text.
It doesn't know what else to do.
So, that's a quick way to solve the problem.
If you're in Excel 2007, we can do this with a brand new function -- new to Excel 2007 -- =COUNTIFS.
COUNTIFS.
First criteria range is all those names in column A, press F4, and see if it's = to ALLEN, and I press F4 1, 2, 3 times to freeze just the column.
Next criteria range is the colors in column B, press F4, and then see if that's = to RED, press F4 twice to freeze just to the row, and copy that throughout the data, and you're good to go.
Now, that's if you're in Excel 2007.
[ =COUNTIFS($A$2:$A$22,$D3,$B$2:$B$22,E$2) ] If you're not in Excel 2007, then we're going to have to rely on either an array formula or sum product.
SUMPRODUCT.
Let's talk about SUMPRODUCT.
So, =SUMPRODUCT and we're going to put 2 arrays here.
We're going to multiply the arrays together, put each array in (). So, in (, check to see if A2 to A22, again, press the F4 key, is = to ALLEN, and press the F4 key 3 times, and then a * sign, *, and then we go check to see if everything in column B, press F4, is = to RED, press F4 twice.
Alright.
So, close that second array, close the whole SUMPRODUCT, and we're good.
We can copy that throughout the data set.
[ =SUMPRODUCT(($A$2:$A$22=$D3)*($B$2:$B$22=E$2)) ] Okay.
Why the multiplication?
The A2 to A22 = to D3 is going to return an array of TRUE and FALSE values, and SUMPRODUCT can't deal with TRUE and FALSE values.
It needs to deal with 0s and 1s.
So, we need to coax that array of TRUES and FALSES into 0s and 1s.
Some people do that by putting a - - before the ( and another - - before this (, separating both by a ,. I feel it's easier just to multiply those together.
That, again, coaxes the TRUES and FALSES to change to 0s and 1s, and it works just as well.
Here's a problem in Excel 2007.
When you type a - -, they autocorrect that into an -. So, you know, for all you - - folks, the * is going to work just as well and achieves the exact same thing.
So, Sergio, thanks for sending in your question.
3 different ways to go: if you just need a quick way to do it, the pivot table; if you need formulas that are going to keep it correct, COUNTIFS in Excel 2007, great way to go; otherwise, you're going back to this relatively difficult SUMPRODUCT to solve the problem.
Well, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today's question is from Sergio in Mexico.
Sergio says, I have two columns -- NAMES in column A, COLOR is in column B. I want to figure out how many times each name occurs with each color.
3 ways to do this.
We're going to talk about all 3 today.
First one, INSERT, PIVOT TABLE.
I want to go to an existing location.
We'll just move it out to the right-hand side here.
Click OK.
Alright.
So, we're going to put NAME down the left-hand side, COLOR going across the top, and then COLOR in the SUM VALUES, and that forces Excel to count because it's a text.
It doesn't know what else to do.
So, that's a quick way to solve the problem.
If you're in Excel 2007, we can do this with a brand new function -- new to Excel 2007 -- =COUNTIFS.
COUNTIFS.
First criteria range is all those names in column A, press F4, and see if it's = to ALLEN, and I press F4 1, 2, 3 times to freeze just the column.
Next criteria range is the colors in column B, press F4, and then see if that's = to RED, press F4 twice to freeze just to the row, and copy that throughout the data, and you're good to go.
Now, that's if you're in Excel 2007.
[ =COUNTIFS($A$2:$A$22,$D3,$B$2:$B$22,E$2) ] If you're not in Excel 2007, then we're going to have to rely on either an array formula or sum product.
SUMPRODUCT.
Let's talk about SUMPRODUCT.
So, =SUMPRODUCT and we're going to put 2 arrays here.
We're going to multiply the arrays together, put each array in (). So, in (, check to see if A2 to A22, again, press the F4 key, is = to ALLEN, and press the F4 key 3 times, and then a * sign, *, and then we go check to see if everything in column B, press F4, is = to RED, press F4 twice.
Alright.
So, close that second array, close the whole SUMPRODUCT, and we're good.
We can copy that throughout the data set.
[ =SUMPRODUCT(($A$2:$A$22=$D3)*($B$2:$B$22=E$2)) ] Okay.
Why the multiplication?
The A2 to A22 = to D3 is going to return an array of TRUE and FALSE values, and SUMPRODUCT can't deal with TRUE and FALSE values.
It needs to deal with 0s and 1s.
So, we need to coax that array of TRUES and FALSES into 0s and 1s.
Some people do that by putting a - - before the ( and another - - before this (, separating both by a ,. I feel it's easier just to multiply those together.
That, again, coaxes the TRUES and FALSES to change to 0s and 1s, and it works just as well.
Here's a problem in Excel 2007.
When you type a - -, they autocorrect that into an -. So, you know, for all you - - folks, the * is going to work just as well and achieves the exact same thing.
So, Sergio, thanks for sending in your question.
3 different ways to go: if you just need a quick way to do it, the pivot table; if you need formulas that are going to keep it correct, COUNTIFS in Excel 2007, great way to go; otherwise, you're going back to this relatively difficult SUMPRODUCT to solve the problem.
Well, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
