Check First Letter in Each Name Against a List of Letters, Then Count. Excel Magic Trick 1850
HTML-код
- Опубликовано: 15 июл 2024
- Download Excel File: excelisfun.net/files/EMT1850....
Learn about how to count how many names in a column start with the letters a, b or c. Then make formula dynamic so it can check any set of letters.
Topics:
1. (00:00) Introduction
2. (00:05) Formula that uses LEFT, an array constant {“a”,”b”,”c”} and the SUM function
3. (00:38) Why the two arrays must be in opposite directions
4. (02:22) Dynamic formula linked to a list in the cells.
5. (02:43) Sumamry, Closing, Video Links
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #count #left #excelfunctions #countifs
Thanks amazing Mike for this EXCELlent video.
You are welcome, Fellow Teacher!!!!
That's very Cool
Thaks Mike, I have my Excel in an ENG GUI, but for me the column divider is "\" and the row is " ; " . Tip: use for example =SEQUENCE(2;3). Then select the formula and press F9 and keep this. So you can see your "separators" ...😉
Thanks, Teammate!!!!!!
Awesome Mike. Great to see u back :) 😊
Great to boomerang back : ) : )
Awesome mike! Thanks.
Glad you like it!!!!!
Amazing Mike, thanks!!!
You are welcome, Chris M!!!!!
This is very useful. Thanks for sharing your knowledge.
Wooow😊 Thanks Mike
You are welcome!!!!!
Amazing trick Mike!!
Glad it is amazing for you : ) : )
Thanks Excel. MikeIsFun
Amazing as always Mike
Glad you like it : ) : )
Double negative converts true/false into numbers? I didn't know that. It is amazing, you are an EXELent teacher.
Glad you like the video. Any math operation that does not change the value can convert: --, *1, +0, /1 and so on.
@@excelisfun You are my hero.
@@nikoletahanakova8573, Glad to help : )
That is just amazing ❤❤.
Glad you like it!!!
Thanks Mike. That was GREAT!!! I guess I am the first in here today. :) :)
You get the first place trophy, Formula Guy John!!!!
another known trick is to use COUNTIF(S) with logic "exists in list" (returns 0 or 1)
=SUM(COUNTIF(G4:I4 , LEFT(B3:B10)))
thus easier to use in filtering
=FILTER(B3:B10, COUNTIF(G4:I4 , LEFT(B3:B10)))
even simulate DAX measure :)
=ROWS(FILTER(B3:B10, COUNTIF(G4:I4 , LEFT(B3:B10))))
Awesome! I added this to download workbook file:
=SUM(COUNTIF(G4:I4 , LEFT(B3:B10)))
I thought we will do byrow(array,lambda(row,or(row))) after making true/false array but each row can only be true only once😅 and that boolean to number always cherry on the top. Really to the point methodology!!
Also, I think new Tocol function can be used to make input characters in rows..
Glad that you liked the cherry on top : ) : )
Thanks mike. EMT Iis come back on tuesday or wednesday...
I do not have set days for posting. But I will have a new one next week and the week after : )
Great stuff! :-)
And if you’d use a BYROW on the conditions matrix you could use that as a filter condition and filter the names themselves instead of counting them. :-)
Nice!!!! It works:
=FILTER(B3:B10,BYROW(--(LEFT(B3:B10)={"a","b","c"}),SUM))
Thanks a lot; would you do the text function custom text format video?
Explain custom text format in details 🙏🏻😇
I have a whole playlist for custom number formatting and TEXT function:
ruclips.net/p/PLrRPvpgDmw0k3Tjz55OfgvaR0ourukzvG
@@excelisfun thanks for your big efforts, the whole list almost about number custom format; I’m wondering if there is “text” custom format 🤔
I was about to ask how to do if you want to test on numerical values, eg >=30 and
Too funny: did you already watch the video that I am supposed to post next week before I published it lol
Anyway, next weeks video is about upper and lower limits for counting numbers and the limitations for PivotTable, FREQUENCY and COUNTIFS.
BTW, my second book, published about 11 years ago did extensive timing of formulas. COUNTIFS and SUMIFS were almost always faster than Boolean SUM. For =SUM((B3:B10>=30)*(B3:B10=30)*(B3:B10=30)*(B3:B10
@@excelisfunInteresting information about execution times. Also, I think it's easier to read if you use COUNTIFS.
@@svenh5752 See you next Monday!~
@@excelisfun :-)
Hi Mike you are the best Excel Guru I have ever Seen , I need a Help How To solve this using Power Query :
this is input : Month Value
Jan-Jun $20
Aug-Dec $30
This is output I wanted :
Mont Value
Jan $20.00
Feb $20.00
Mar $20.00
Apr $20.00
May $20.00
Jun $20.00
Jul $30.00
Aug $30.00
Sep $30.00
Oct $30.00
Nov $30.00
Dec $30.00
That is hard.
Here is a way that does not consider the year:
let
Source = Excel.CurrentWorkbook(){[Name="MonthDollarData"]}[Content],
AddDataTypes = Table.TransformColumnTypes(Source,{{"Data", type text}}),
ExtractValue = Table.AddColumn(AddDataTypes, "ExtractValue", each Number.From(Text.AfterDelimiter([Data], "$")), type text),
CreateList = Table.AddColumn(ExtractValue, "CreateList", each
let
m = {"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},
bmp =List.PositionOf(m,Text.BeforeDelimiter([Data],"-")),
emp =List.PositionOf(m,Text.BetweenDelimiters([Data],"-"," $"))
in
{bmp..emp}),
ExpandList = Table.ExpandListColumn(CreateList, "CreateList"),
LookupMonth = Table.AddColumn(ExpandList, "LookupMonth", each
let
m = {"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}
in
m{[CreateList]}),
RemoveDataCreateListColumns = Table.SelectColumns(LookupMonth,{"LookupMonth", "ExtractValue"})
in
RemoveDataCreateListColumns
Great You are amazing
@@Datavers Thanks!
Thanks a lot, i have been watching your videos for a while now. I am currently looking to learn everything about charts, does the playlist " Excel Chart tricks" contain all or most of the videos?? if not can you please update it or are there any other playlist or vids?. Thank you.
I just added some new chart videos to playlist to update it. Just the two at the top of the playlist will cover all you need to know:
ruclips.net/p/PL74414F91C42EEA6E
Thanks a lot 🙏🙏, even though you're busy, you still take some time reply to most of the comments, i really appreciate it.@@excelisfun I'll be having a test in a few weeks where our teacher will give us a data and we hav to visualize it then and there, your videos will be helpful. thanks
There are lots of videos in your channel arrange in playlist. But I am confused from where to start learning. Would you suggest me from where should I start watching your videos. Should I start from the oldest?
Mike has a book that might be better for learning from. It's from beginner to advanced. Otherwise, the best method is to use Excel until you get stuck and then refer to Mike's videos to get help with your particular problem.
Start with the Excel Basics Playlist, then go to the advanced one or MECS. Here is channel into video that explains it all: www.youtube.com/@excelisfun
Thanks MIke ....I just wanted to point out how it is easier to insert HSTACK("a";"b";"c") instead of {"a"\"b"\"c"} ... 😏
If you use a QUERTY keyboard and a decimal point, you don’t have that problem. ;-)
How is it easier?
@@excelisfun not everyone knows how to put brackets ... and indicate the comma as the separator.
HStack is more intuitive
@@sscire Got it : )
Hello pls, I work on names data, mostly the name arrangement from our software don't match the name list from our customers. Please is there any way where if can use excel to compare the names irrespective of the arrangement of the name.
Thank you. We are currently using office 2016.
Good morning, it Is posible, to make in office 2013?
Yes, the formula works in any version of Excel.
thanks mike , i want to help me if we want count "a" in all array# i try to make it but there is error
=MID(B3:B10,SEQUENCE(,MAX(LEN(B3:B10))),1)
it will result array and if i add countif() it result error by that =COUNTIF((MID(B3:B10,SEQUENCE(,MAX(LEN(B3:B10))),1)),"a")
but if i do =COUNTIF(K4#,"a") it success
k4# that array resulted from =MID(B3:B10,SEQUENCE(,MAX(LEN(B3:B10))),1)