Shoutin’ Sam has an Excel question.
This video shows many ways to answer different questions.
=FILTER() in Excel
=XLOOKUP in Excel
=TEXTJOIN in Excel
=INDEX(FILTER()) in Excel
Category: Excel
Excel Sum The Comma Delimited Numbers From One Cell – 2372b
Om asks: I have 123,413,413,1352,244 in a single cell.
There are many numbers with commas in between in each cell.
How to Sum all the numbers using a formula?
I don’t want to use Text to Column.
By the way, it is Excel 2010.
I use Brad Yundt’s three-line VBA function to split the numbers.
Then Excel formulas to sum them.
Also, an easier formula if you have Microsoft 365.
Table of Contents
(0:00) Sum all comma-delimited numbers in cell using an Excel formula
(0:19) Easy solution with TEXTSPLIT
(0:32) Switch to VBA for Brad Yundt Splitter code
(0:55) Using SPLITTER custom function in Excel
(1:25) Wrap up with Wally & Nancy
Numbers 1 to 100000 in Excel #Shorts
YouTube viewer asks: How to generate numbers 1 to 100000?
There are many ways to do this in Excel. I will show you two methods in this short video.
Power Query Insert Blank Row After Each Group – 2372
Back in episode 2359, I needed to add a blank row after every group of names. Although I was already in Power Query, I went out to Excel to add the blank rows.
Several people commented with code to add a blank row after each group in Power Query. Thanks to @BillSzysz1 @radosawpoprawskiyourfriend769 @GeertDelmulle Rico S and Suat Ozgur for guiding me on how to add one line of M code to my query.
I am at that stage in my Power Query journey that I prefer to do everything in the Power Query editor. Once I switch over to the advanced Continue reading “Power Query Insert Blank Row After Each Group – 2372”
Excel Count Super Bowl Squares By Name – 2371
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 Continue reading “Excel Count Super Bowl Squares By Name – 2371”
New Excel Feature Annoying Half Of All Excellers #shorts
Why is there a thick dark box around the formula bar in Excel?
Or, have you never noticed it?
It is part of the F6 Loop.
Busn 218 – Advanced Excel – Winter 2023 Quarter Introductory Video
Busn 218, Advanced Excel class taught by Excel MVP Mike Girvin at Highline College.
This is the intro video for matriculating students at Highline College in winter quarter, 2023.
To watch at YouTube here is the playlist:
https://www.youtube.com/playlist?list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW
Excel Statistical Analysis for Business – Busn 210 – Winter 2023 Quarter Introductory Video
Busn 210, Excel Statistics for Business class taught by Excel MVP Mike Girvin at Highline College.
This is the intro video for matriculating students at Highline College in winter quarter, 2023.
To watch at YouTube here is the playlist: https://www.youtube.com/playlist?list=PLrRPvpgDmw0m3oqpp1XcPuaxyM4Bpi0dN
Excel Formula To Extract Country From Phone Number – 2370
Is there an Excel formula that can return the country name from the telephone number?
This video shows you how. To make your life easier, add this worksheet with five named ranges already in it:
https://1drv.ms/x/s!As7G72Sl487Jlj1AUGli1Zgx0OgW?e=0Bda0m
Excel Holidays Between Two Dates – 2369
After episode 2363, a pair of great questions and a tip.
Tip 1: When specifying the Holiday Range in Excel, make the range into a table and then name the range. That way, when you add new holidays at the bottom, the named range will automatically expand.
Question 1: How many holidays fall between two dates?
Question 2: What is the list of holidays between two dates?
This video includes details on NETWORKDAYS.INTL and FILTER functions.
You will also see how I take two sub-formulas that were in different cells and combine them into a single formula by scooping the characters out of each formula.
Table Continue reading “Excel Holidays Between Two Dates – 2369”
My Poem – An Ode to Excel
Written by Magic AI and I
Oh Excel, you’re a sight to behold
Your powers so vast, your secrets untold
Your formulas and charts, they never grow old
Your uses are vast, your features unfold
Your beauty’s unmatched, your power’s divine
Your data so precise, and figures so fine
From pivot tables to macros, you make life sublime
A spreadsheet maven, your use is so prime
Oh Excel, you make the mundane sublime
Your features so strong, all those bold lines
From tracking expenses to uncovering goldmines
You make the mundane task feel more like a sweet rhyme
Your power and ease, it’s hard to describe
Your talents so numerous, they stretch as far as the eye can see
You make the task of data entry so much less of a chore
So, Excel, my dear partner and friend, I thank you for making my work life, oh so much more
Excel How To Create A PDF Of Just One Page – #shorts
Mary Ellen asks: It is easy to print one page of an Excel print range. But how do you send just one page to a PDF? The answer is there, but hidden.