A question from @chwang06 : Asks how to count Super Bowl Squares by the name in the squares.
In this video, I will show how to get a list of players with =UNIQUE(SORT(TOCOL(C3:L12))) and then count how many times each person played using =COUNTIFS(C3:L12,C17#)
Table of Contents
(0:00) Question: Count Excel Super Bowl Squares by Name
(0:35) Make sure your Excel has TOCOL function or use Excel Online
(0:55) Excel.New in Excel Online
(1:05) Unwinding Excel range to 1 column vector
(1:33) SORT and UNIQUE functions
(2:00) COUNTIFS with Dynamic Range as second argument
(2:30) Many names for # symbol
(2:51) Mention video 2385
(3:00) Wrap up with Wally & Nancy
In this video, I will show how to get a list of players with =UNIQUE(SORT(TOCOL(C3:L12))) and then count how many times each person played using =COUNTIFS(C3:L12,C17#)
Table of Contents
(0:00) Question: Count Excel Super Bowl Squares by Name
(0:35) Make sure your Excel has TOCOL function or use Excel Online
(0:55) Excel.New in Excel Online
(1:05) Unwinding Excel range to 1 column vector
(1:33) SORT and UNIQUE functions
(2:00) COUNTIFS with Dynamic Range as second argument
(2:30) Many names for # symbol
(2:51) Mention video 2385
(3:00) Wrap up with Wally & Nancy
Transcript of the video:
It's that time of year. We're using Excel for something really important.
Tracking the Super Bowl Squares.
Every single bar in America right now with locals has a 10 by 10 grid on the wall, and people are writing their initials in there, paying 10 bucks a square, and they'll split it up based on the score at each quarter of the big game on Sunday.
So a question from Twang06, "Is there a way to create a dynamic table to count the names in the squares?
For example, HHT bot 6, VQD bot 2?" All right, so here's my Super Bowl Squares.
Caution: I'm going to use a function here. The two call function that is pretty new.
You're going to need Microsoft 365.
And if your Excel doesn't have the TOCOL function, you might want to open Chrome in the address bar, type "Excel.new," and that'll gin up a new version of Excel online. It will have that function.
All right, so the first thing we're going to do is we're going to take that 10 by 10 square and make it into a single column that's =TOCOL. So we're converting that to a column.
I'm going to do control shift up, control shift right, and then shift down to select the 100 cells.
That takes the items that we see here across the first row and just moves them down. This creates a hundred cells.
All right? Now we have that ...
well, let's sort it.
I guess it'd be nice to have it sorted. So we'll sort that.
So SORT, we'll sort it alphabetically and then get a unique list of each name appearing once.
So there's a kind of cool function, the unique of the sort of the TOCOL of the range of squares.
And then to count each one.
So we're going to count ifs and we're going to look through the hundred cells.
We don't need to press that four there or lock it down because we're not copying this formula anywhere. And then the criteria one.
We're actually going to use every cell over here to the left, so point to C17 in my case. Yours might be in a different spot.
And then type a hash symbol, also known as the number sign, in America, the pound sign, the octothorpe, the sharp character, whatever you want to call that, and close paren.
And so there we are. JFK had 35, LBJ had 23, that's me at seven.
So you can see how many times each person played or maybe collect their $10, although you should have done that when they put their name in. All right, so great question.
I love the Super Bowl Squares game.
Check out my old video, 2385, if you're playing the rocket mortgage Super Bowl Squares for a big analysis of that.
Well, thanks to Twang06 for sending that question in.
Thanks to you for stopping by. Good luck to your favorite team this weekend.
We'll see you next time for another netcast from MrExcel.
Tracking the Super Bowl Squares.
Every single bar in America right now with locals has a 10 by 10 grid on the wall, and people are writing their initials in there, paying 10 bucks a square, and they'll split it up based on the score at each quarter of the big game on Sunday.
So a question from Twang06, "Is there a way to create a dynamic table to count the names in the squares?
For example, HHT bot 6, VQD bot 2?" All right, so here's my Super Bowl Squares.
Caution: I'm going to use a function here. The two call function that is pretty new.
You're going to need Microsoft 365.
And if your Excel doesn't have the TOCOL function, you might want to open Chrome in the address bar, type "Excel.new," and that'll gin up a new version of Excel online. It will have that function.
All right, so the first thing we're going to do is we're going to take that 10 by 10 square and make it into a single column that's =TOCOL. So we're converting that to a column.
I'm going to do control shift up, control shift right, and then shift down to select the 100 cells.
That takes the items that we see here across the first row and just moves them down. This creates a hundred cells.
All right? Now we have that ...
well, let's sort it.
I guess it'd be nice to have it sorted. So we'll sort that.
So SORT, we'll sort it alphabetically and then get a unique list of each name appearing once.
So there's a kind of cool function, the unique of the sort of the TOCOL of the range of squares.
And then to count each one.
So we're going to count ifs and we're going to look through the hundred cells.
We don't need to press that four there or lock it down because we're not copying this formula anywhere. And then the criteria one.
We're actually going to use every cell over here to the left, so point to C17 in my case. Yours might be in a different spot.
And then type a hash symbol, also known as the number sign, in America, the pound sign, the octothorpe, the sharp character, whatever you want to call that, and close paren.
And so there we are. JFK had 35, LBJ had 23, that's me at seven.
So you can see how many times each person played or maybe collect their $10, although you should have done that when they put their name in. All right, so great question.
I love the Super Bowl Squares game.
Check out my old video, 2385, if you're playing the rocket mortgage Super Bowl Squares for a big analysis of that.
Well, thanks to Twang06 for sending that question in.
Thanks to you for stopping by. Good luck to your favorite team this weekend.
We'll see you next time for another netcast from MrExcel.
