Salama from YouTube has two columns of numbers. The area in column A is from 1 to 36. The month in column B is from 1 to 12. How many times does each combination of Area and Month happen?
Today's video offers three methods.
Table of Contents
00:00 Problem and solving with SUMIFS
01:27 Solving with a pivot table
02:25 Solving with three dynamic arrays
Today's video offers three methods.
Table of Contents
00:00 Problem and solving with SUMIFS
01:27 Solving with a pivot table
02:25 Solving with three dynamic arrays
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2420.
The frequency of a combination of two columns.
Great question sent in from YouTube.
The person has two columns.
Area code is the numbers 1 to 36.
Month is the numbers 1 to 12.
I created a fake data set here with 50,000 rows. They want a matrix that shows the combination – the count of how many times that code 5 month equal 5 occurred.
Alright so across the top we want the numbers 1 to 12.
Type the number one.
Hold down the control key.
And drag over 12.
Down the left hand side, type the number one.
Grab the control key and drag down to 36.
This formula - and the dollar signs are very important in this formula.
We will count if everything in column A. Dollar Sign A. Dollar Sign 2.
Colon.
Dollar Sign A. Dollar Sign. whatever your last row is.
Is equal to E12 with a single dollar sign before the E.
So that way as we copy it to the right, it continues to point to column G.
The second criteria.
Look through column B. That’s B2 to B50001.
Again with the four dollar signs and see if it's equal to F11.
This time a single dollar sign before the 11.
I'll copy that across so Control+C. Control+V. Double click the fill handle.
And just to add a little bit of meaning, Conditional Formatting, Color Scales, the second color scale like that.
The second method, I think, is far easier.
Choose one cell in your data, Insert, Pivot Table, on an existing worksheet.
Let's go right here.
Click OK.
In the pivot table we want area code in the Rows.
Month in the columns.
And then check this out: Month in the values.
That initially gives us sums, which is not what we want.
But what we're going to do is click this first cell here under the one one and go to field settings and change to a Count.
Click OK.
Now a couple of things to clean this up a little bit.
First off, right click on the Grand Total heading and Remove Grand Total.
The same thing down here.
Right click on the grand total heading.
Remove grand total.
If you want the conditional formatting, we should be able to apply that.
Perfect.
That I think is easiest way.
If you have Office 365, there's a third way.
Three formulas.
The formula right here is =SEQUENCE(1,12).
That gives us those 12 numbers.
The formula here is =SEQUENCE(36) gives us those 36 numbers.
And then a single formula here gives us the entire matrix.
We don't need dollar signs because we're not copying the formula.
Look through everything in column A. See if it is equal to a H12#.
Look through everything in column B. See if it's equal to AI11#.
That hash says, look at the whole array.
I will get the same answers as before.
All right, three different ways to solve the problem.
Check out my new book, MrExcel 2021.
It definitely covers pivot tables.
Click the I in the top right hand corner.
If you like these videos, please, down below, click Like, Subscribe, and Ring the bell.
Feel free to post any questions or comments in the comments below.
Well, I want to thank you for stopping by.
I will see you next time for another netcast from MrExcel.
The frequency of a combination of two columns.
Great question sent in from YouTube.
The person has two columns.
Area code is the numbers 1 to 36.
Month is the numbers 1 to 12.
I created a fake data set here with 50,000 rows. They want a matrix that shows the combination – the count of how many times that code 5 month equal 5 occurred.
Alright so across the top we want the numbers 1 to 12.
Type the number one.
Hold down the control key.
And drag over 12.
Down the left hand side, type the number one.
Grab the control key and drag down to 36.
This formula - and the dollar signs are very important in this formula.
We will count if everything in column A. Dollar Sign A. Dollar Sign 2.
Colon.
Dollar Sign A. Dollar Sign. whatever your last row is.
Is equal to E12 with a single dollar sign before the E.
So that way as we copy it to the right, it continues to point to column G.
The second criteria.
Look through column B. That’s B2 to B50001.
Again with the four dollar signs and see if it's equal to F11.
This time a single dollar sign before the 11.
I'll copy that across so Control+C. Control+V. Double click the fill handle.
And just to add a little bit of meaning, Conditional Formatting, Color Scales, the second color scale like that.
The second method, I think, is far easier.
Choose one cell in your data, Insert, Pivot Table, on an existing worksheet.
Let's go right here.
Click OK.
In the pivot table we want area code in the Rows.
Month in the columns.
And then check this out: Month in the values.
That initially gives us sums, which is not what we want.
But what we're going to do is click this first cell here under the one one and go to field settings and change to a Count.
Click OK.
Now a couple of things to clean this up a little bit.
First off, right click on the Grand Total heading and Remove Grand Total.
The same thing down here.
Right click on the grand total heading.
Remove grand total.
If you want the conditional formatting, we should be able to apply that.
Perfect.
That I think is easiest way.
If you have Office 365, there's a third way.
Three formulas.
The formula right here is =SEQUENCE(1,12).
That gives us those 12 numbers.
The formula here is =SEQUENCE(36) gives us those 36 numbers.
And then a single formula here gives us the entire matrix.
We don't need dollar signs because we're not copying the formula.
Look through everything in column A. See if it is equal to a H12#.
Look through everything in column B. See if it's equal to AI11#.
That hash says, look at the whole array.
I will get the same answers as before.
All right, three different ways to solve the problem.
Check out my new book, MrExcel 2021.
It definitely covers pivot tables.
Click the I in the top right hand corner.
If you like these videos, please, down below, click Like, Subscribe, and Ring the bell.
Feel free to post any questions or comments in the comments below.
Well, I want to thank you for stopping by.
I will see you next time for another netcast from MrExcel.
