Mr Excel & excelisfun Trick #3: Wildcard Count

[2107Gopal]

It was a great lesson for me, but i have a question.

What if a cell contains more than 1 similar character, because then its not working.

[ExcelIsFun]

Anytime you do a partial match like this, it is not possible to get it to work 100% of the time. Such is the nature of a partial match. I do not know how to solve this problem.

[xmcMaEc]

@ExcelIsFun why don't you use a text formula to breakdown the Service records by columns? 1 column per letter, example column1.type will be D, Column2.type will be M and column3.type will be P and at the end you culd use boolean logic or countif's to get that exact count?

[ExcelIsFun]

Which ever method works... COUNTIF with wild card seems an easy way.

[darrencornell1]

this is so cool

[ExcelIsFun]

I am glad that you like it!

[edcabading]

Hello Mr. Girvin and Mr. Excel,

I've tried using =DCOUNTA(data base, column number of field to count, criteria)

NOTE : that criteria 'CONTAINS" is FILED NAME ON TOP THE NEXT ROW =*M*, NEXT ROW =*D* THEN NEXT ROW =*P*

[TheMrBenjamin]

Hey,

You guys should do a tour in London. I attended an Excel course but my tutor ddnt know half the stuff you guys do let alone be able to teach it effectively.

[ExcelIsFun]

That would be fun! Send a note to MrExcel and let him know! He is bjele123 at YouTube.

[SFDSea]

I was pleased to know that you are in WA, I am in Redmond and my wife went to Highline. Thank you very much for your work. You two are awesome. Hope one day I can meet you and talk about Excel ;-0

[ExcelIsFun]

You are welcome for the videos! Highline is a great place to go to school and work! You can come and see me during my office hours or make an appointment to come and have fun with Excel in person. Or you can have the most fun ever and come and take my Busn 214 class in spring which is ALL of Excel in 11 weeks, live in person!

[ExcelIsFun]

Busn 214 is a grea class. Come and in it in spring quarter! The class is usually taught at noon so working people can take it during lunch. Just tell your boss that the time invested by the business is well worth the extra productivity that you will bring to the business!

[smumin160]

GREAT JOB!!!!

[ExcelIsFun]

Dear smumin160,

Thanks from Mr Excel and excelisfun!

[TiffanySouthall]

I like learning multiple solutions for the same problem in a short amount of time. You guys are awesome. The jon funtion, ampersand, and wildcard were new to me but the intorduction to them is appreciated. Can you do some videos showing more the symbols i.e @!#%^&

[ExcelIsFun]

Dear TiffanySouthall, I am glad that the video is helpful! I have videos for all the topics you asked about. See the below videos. Here is a video about e: YTLE#99: The Number e in Excel about !: Excel Magic Trick #182: Workbook References about %: YouTubersLoveExcel#16: Percent Number Formatting Tricks about ^: Excel Busn Math 02: Formulas and Math Operators about &: Excel Magic Trick # 166: CONCATENATE function & Ampersand --excelisfun

[TiffanySouthall]

I did some really fun things with the CONCATENATE function I think I may have used an Amersand for the first time.

I used it organize some address from a list of data string. REALLY AWESOME!! Your video about it really helped. Cant wait to see the next video.

[ExcelIsFun]

Dear TiffanySouthall,

Wow, that's cool becasue I can't wait to make the next video!!! Making videos with Mr Excel makes Excel even more fun!!

--excelisfun

[MILNERFAN]

Great job Mr excel and excelisfun!

[ExcelIsFun]

Dear MILNERFAN,

Thanks!!!

--excelisfun

[timmycook58]

Mike you definitely had the better way in solving this problem. MIKE FTW!!

[ExcelIsFun]

Dear timmycook58,

Thanks!

But Remember:

Only in rare cases is there a definitive BEST. Context always determines what is best or good. The two methods that Mr Excel showed are clearly the best for most Excel users (The vast majority of people do not know how to use wild or the ampersand). The method that I used is certainly good because it shows all three at once.

Well keep making the duo-dual videos!

--excelisfun

PS I do not know what FTW means...

[kayno9]

hey Mr Excelisfun i like the duelling. nice to see u different styles for the same problem. Also nice to see great respect for one another. Great sportsmanship!

[ExcelIsFun]

Dear kayno9,

I am glad that you liked it and liked the different styles. It sure is fun to do!!

I certainly have the greatest respect for Mr Excel because he is the one that started the whole online-Excel-videos-Message-Bo­ard thing! I got the idea to do Excel videos online form him!!!!

It is so much fun to make videos with him! We will keep making more!

--excelisfun

[ExcelIsFun]

Dear Team,

3 methods to make different sorts of people happy:

1) Filter & SUBTOTAL solution good for: a) Don't mess with criteria syntax b) Works in all versions

2) Table & SUBTOTAL solution good for: a) Don't mess with criteria syntax b) Works only in 2003 & 2007

3) COUNTIF and criteria syntax solution good for: a) Mess with criteria syntax b) Works in all versions

--excelsifun

[uniznguyen]

the screen is too small, i can't see anything

[ExcelIsFun]

Dear uniznguyen,

Sorry about that - the first part is small, but the last two parts are big. We'll try to make it bigger in later videos.

--excelisfun

[JPB6969]

I love these podcasts! It's interesting to see different solutions to the same problem.

[ExcelIsFun]

Dear JPB6969,

Great!! I think we are going to keep doing them! All I can say is that they are very fun to do and working with Mr Excel is the best!!!

--excelisfun

[SheLuvsHerIpod]

This is so cool - thanks for demonstrating.