How to Count Cells With Text in Excel (Mysterious Formula as NEVER Seen Before)
HTML-код
- Опубликовано: 15 июл 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
In this video you'll learn about a super short formula in Excel to count cells with text and ignore blank cells, numbers and also formula cells that result in blank cells or empty strings.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/count-text-...
This is one of a kind Excel formula hack.
I received this special syntax for the Excel COUNTIF function from Jack, one of our community members. It works perfectly although I have no idea why. Because it looks like we are applying a wrong Excel syntax (angry eyes). If we were to write the formula "correctly" it would only exclude the truly blank cells. But cells that include formulas which result in blank cells would still be included in the count.
Alternatively, we can use the Excel COUNTIF function with the placeholders ? and *. This Excel count formula which uses wildcards, will also also just count non blank cells and ignore formulas that result in blank cells as well as ignore zeros and other numbers.
In a situation when we want to count all cells that include either text or numbers we can use SUMPRODUCT in combination with the LEN function. This way we can check if the length of the content in the cells is at least 1 character. You can replace SUMPRODUCT with SUM if you have the new Dynamic Array Excel - if you have legacy Excel and use SUM instead of SUMPRODUCT you need to press Control+shift+enter.
Key Insights:
- Rare Excel Syntax Discovery: Unearth a special syntax in Excel formulas that might have escaped even seasoned Excel users. Learn about a formula technique that's not commonly known.
- Contextual Challenge: Understand the practical challenge of counting text values in Excel, excluding numbers, blank cells, and formulas resulting in empty strings.
- Formula Demonstration: Watch as we test this unusual formula, achieving accurate text counts and excluding non-text elements.
- Alternatives Explored: Compare the mysterious formula with more traditional methods like wildcard characters in the COUNTIF function and the versatile SUMPRODUCT formula.
- Practical Application: See how these formulas can be applied in real-world scenarios, enhancing your data analysis and Excel efficiency.
00:00 How to Count Text Cells in Excel
02:07 Excel formula hack to count text cells
02:52 Alternate Excel formula to count text cells (with wildcards)
03:49 Count all cells except the truly blank cells in Excel
04:30 Count all text cells and numbers but exclude blank cells and formulas that result in blank cells
Excel hacks and tricks playlist: • Excel Hacks: Tips & Tr...
★ My Online Excel Courses ► www.xelplus.com/courses/
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
Grab the file I used in the video from here 👉 pages.xelplus.com/count-text-file
It looks like Igor Baca solved the mystery! This is in his words: "It doesn't seem to have anything to do with ASCII order ... I printed ASCII signs as CHAR(1 to 100) then copy/paste as values in another column. Then converted numeric characters to numbers and SORTED the column alphabetically. In next column I gave a condition ">RC[-1]>"" sign. So the closest match you can get to count text only would be =COUNTIF(range,">>") because the ">" character is the last before text characters start."
I also tested this until character 255 and Igor's theory holds. The sorting order is based on how Excel sorts the characters and not based on the code. Tilde has character code 126 and it's sorted before ">" and all the usual alphabetic alphabetic characters are after the >.
can you make a video about this solution explaining it?
Notice that using COUNTIF(range,">"&""&"!") you will count the text cells in the range. Thus we see that COUNTIF(range,">
COUNTIF(range,">" is Comparison operator and "
Thanks a lot for keeping us surprised. You know what I wondering about and wandering the Web to know! The bugs. The formulas that gave us solutions for many problems sometimes may lead us to wrong area unknowingly. So I request you to explain the possibilities and the solutions or prevention for those trouble making situations, if you could please. For example, vlookup for double entries.
Hi Leila, You have demonstrated wildcard characters "?*" together in this session, but when I separately do it I get the count for text (only with "*") and count for numbers (only with "?"). Am I using this function correctly? Please let me know. I also use counta function instead of combo sumproduct and len. Thank you.
As Cladiou Spadacini, I Ahmed and others say below, COUNTIFS seems to be looking at the ASCII character 60. However, when I use the formula (with "angry eyes" criteria ">
Thanks Mike for your input and testing. You're right - it skips a bunch of characters after 60. I think Igor got it. If we sort the hardcoded version of the characters generated until 255, and then sort on character (after converting to number), then all the characters that are seen by the syntax fall after the > sign and all the other ones fall before it. The sorting seems to be on how Excel sorts these characters and not on the character code.
@@LeilaGharani The internal Excel engine sort does crazy things again. That is really amazing!!!!
I spent almost two hours to resolve this small but mighty issue but I thought of checking your page out. This really help, many thanks
Although I've been in the field for a long time, but really the content you provide is unique and easy to get adopted.
Thanks Leila, specially for this First-Time to know formula string
I love the knowledge you share in your videos. Thank you. I've learned so much over these last months.
I have needed =COUNTIF(range,"") for a while now. You have saved me from so many future headaches. Thank you!!
I just used the mystery formula with "Countifs" (I had two arguments to test against) and it worked like a charm (I backtested separately). I had about a total of 2500 records and 1000+ with text to be sorted in 50+ categories, so I wanted to count how many of these 1000+ text records belonged to each of categories. Thanks for this one, and of course for the hundreds of other great tutorials!
You produce some of the best videos. Always very clear and specific. I play with the download file and watch your videos over and over till is sinks in!!!! Thanks!
Wow, thank you, Mireille!
Thank you! You're such a great teacher.
I'm from Colombia and learning about excell.
If Mr Excel and Leila dont know the answer to something in Excel, I very much doubt anyone else would!! Great job as always!
Thank you so much for this tutorial!! I've been searching on countless websites and forums, looking for this particular formula, and I finally found it, thanks to u :D You got a new subscriber!
That's great Monica. Glad to have you here.
Thanks for sharing Leila, today my manager had a work situation that I solved using this formula. I'm am happy that I had watched your video yesterday ☺️
Why is evrytime I watch your videos, I find myself being unaware of so many things excel can do! This is actually and amazing feeling to discover something new eveytime. Thanks Leila for being the guiding light😋
Glad you find something new here :) That's the great thing about this community. We all keep learning new things.
Thank you! This is just the formula I needed today! Keeping sharing!
Great thanks Leila for sharing this mysterious formula! I am facing this same problem when I am creating a dashboard. It saves my time..thanks again.
You've done it again - excellent video !
That’s a great tip.. Lelia 👍🏻 never use it.. but just explore now with example on workbook.. it look like ">” it is treating as Operater & " than one & it dose the count..
As usual, excellent video.
Excellent, thanks a lot 👍👍👍👍👍
Thanks a lot it helped me count ifs formula as well!!
You are really gem of Excel.. Kudos...
Amazing! As usual 😊 thanks alot
Thank you. It solves my problem.
This is an awesome hack....I like this type...please post more of this kind.
brilliant trick.. please keep sharing.
I have never seen this!!!! Thanks Leila
in fact i needed today but i had to use long formula waw great to learn this thank you
Wonderful as always
this is great! thank you
Well done, good explanation.
Das ist so cool, Leila Ich habe noch nichts von dieser Syntax gehört. Viele liebe Grüße 🌞
Nice. You are simply amazing
I have tested that formula in Apple’s Numbers has well and it works too! Interesting hack.
Thank you so much
Cool . These all syntax are new to me 😊
THANK YOU SO MUCH FOR THIS VIDEO. THIS SAVED ME
Thank you liela for all these usefull formula
Your skills are really amazing.
Just awesome!
Till date ur each n every video has always been thumps up.
you are awesome i just solved a question related to this formula, thank u
Thank you so much. Finally, have the solution.
It's helpful!
very good tip!!!
great mentor A huge respect from Pakistan
Hi Leila. An awesome and interesting tip! Thanks for sharing :)) Thumbs up!!
I had come across "" before and it confused me a bit. I hadn't come across ">
Thank you 🙏
Excellent. I can use this.
Thank you very much, today I learned to calculate things in report.
Thanks Leila.
Thank you Leila
Amazing video well done 😊😊❤️❤️👍👍
superb thank you
U r genius. Really amazing
Hi, Leila, this helped my problems for years of workarounds. Thank You.
This is how I modify it. timeframe 5:16, formula: =SUMPRODUCT(--(len(D4:D13)>0))
Modified Count Formula to fit my needs of counting comments, but discarding the numerical value '-' (the MINUS sign) I use to shorten lengthy cells of the left.
The modified formula: =SUMPRODUCT(--(LEN(W6:W55)>1)) REPLACING the zero of your formula for a number 1 does not count the minus (-) sign on my cells.
GREAT for my years old problem with too many excel sheets.
Thanks for sharing this, Rico!
one I will remember!! good discovery
Thanks you sister. You are brilliant girl.
Beautiful
Perfect, I've been looking for this exact formula... thanks!
Happy to help!
@@LeilaGharani =">
thanks a lot
Very useful
Leila (et al), I just used the 'angry eyes' mystery syntax and it worked like a charm! As far as use case, it was much like your example. The formula scanning e-mail subject lines deciding what to review later and which can be discarded. THANK YOU, THANK YOU, THANK YOU
BTW - 'Angry Eyes' was a '70's genre song by Kenny Loggins and Jim Messina...worth a listen.
Glad it's useful Steve. Now, I'm gonna check out that song :)
FYI, based on your insight about the "solve" from 3 days I ago I started playing around and found some additional weirdness.
If I change the formula to COUNTIF(RANGE,CONCAT(">",CHAR(60)) I get the same results as you but if I change it to COUNTIF(RANGE,CONCAT(">",CHAR(57)) then I get nothing back. I did this because I wanted to include additional characters in the count. However, if I further tweak it to COUNTIF(RANGE,CONCAT(">=",CHAR(58)) it works again.
Wow Leila, I never knew ">
Great Stranger Tip for the first time i see >< Thumbs up
Hi Leila, thanks once again for this interesting tip. I will try it on Mac version. I have one question for you (or anyone else who could help): Is there a way to count a certain format color of an array of cells on Excel 365 for Mac?
Thanks.
Thanks
got it....thanks!!
Hello Leila,
As this >< calculates only Alpha values that it means it is checking the logical number of < less than > or greater than.. which technically in mathematics can only be done for numbers. Thus if the value in a cell is not a number that can be compared, it will count it. - This may be a logic; but not sure.
I really searching for a formula as same. Got it. Wow....
superb madam 👍👍👍👍👍👍👍👍👍
it was superb ms liela I want to learn advance excel nd vba with you
Liked it 👍
Thank you!
Could you please make a video of OLAP, Pivot Table?
thanks.....
Appclause really i appreciate ur hardship...
Worth watching
I'm glad you like it Muhammad.
@@LeilaGharani i have one major file we use it on monthly basis so that was prepared by my friend so o don't know how he done so can share it with you for helping me
Thank you Leila,
For counting written texts + numbers, you can use the function COUNTA directly, so no need to go for LEN and SUMPRODUCT nested formula.
For everyone info, I summarized the subject as below (I used dummy range A29:A37):
=COUNTIF(A29:A37,"*") Counting written text + formula text result (including emty string result "" and space result " ")
=COUNTIF(A29:A37,"?*") Counting written text + formula text result (excluding empty string result "", but including space result " ")
=COUNTIFS(A29:A37,">
I am addicted to these tutorials! Can you tell me what which is the best version of excel available? I really need one with all the bells and whistles especially bar code font. Thank you for making these videos.
Excel 365
Thats a good trick, I need to use that sum product feature though on filtered data !!
To count specific text in a cell range and ignore all hidden text strings in a formula (""), place the text between two wildcards (*) then in-between two quotes (").
Example: =COUNTIF(I2:I29,"*
The formula syntax would count not just text but all characters with ASCII value more than 60. Because the criteria is > (GREATER THAN operator) the sign < (which has the value of 60).
Thks Leila :-)
First and formost, I would like to thank you for your videos. They have been so helpful over the past couple of years. With regard to the "Angry Eyes" - I have been programming in a rather unknown 4gl language since the early 1980's. When testing to see if they have entered a numerical value, I use an IF statement that "If object >= " ", Then Error". In other words, If the value of the object is < a " ", it is numeric in value, otherwise it is an alpha character. Is it possible that Excel is looking at ">
Great !
For the whole unicode characters range (55k symbols) this countif( ;">
You just saved my life
What a hot formula this is...
get well soon
Exclusively exclusive
LG is gr8
Thank you for such a wonderful lesson..
I have a question
Let's say there is a data with 50 column and I need to present one report using the data from the master file but only with 15 column(and these column is same as the master filel. How to do it instead of copy and paste. Can you please help.
I use excel 2016
I love your training, I have create a purchase form that connect with a purchase record sheet and also with my charts of account sheet, so here I want to create a system when I purchase an item, I want it will pay from my selected account like bank, cash box or personal accounts is that possible
Hello, Dear Leila Gharani. I am fond of the Excel tutorial videos and hacks that you prepare. Recently I have created an interactive and dynamic excel workbook with the help of this channel. Thank you for all these fantastic Excel hacks. I just admire your skills and knowledge and I am really getting motivated watching you. Now, I have a question interests me for several days. So, I have a table consist of exam marks and I want to rank them in ascending order. I used LARGE function, but it was not that useful, and I shifted to better RANK function. Still it has problems. For example, it gives me the same result for same amount fo point, thus I have (for instance) two first places. I am fine with that. However for the next result, the output is 3, not 2. It is not suitable for me. I just looked for solutions in internet, but tuy are all manual, unfortunately. I am wondering if you have ve video or advice that help me to get an order for results, that does not miss the places in order and give adjacent numbers. I hope, I could explain my problem and looking forward for your answer. Have a good day.
Get well soon...
Great leila , see here the formula =COUNTIF(C5:C16,"
Hi, not sure if this has been covered already but its really easy to understand if you look at what you are asking, excel has every character in an order, you can see this order by typing in char(1) and increasing the number down a sheet, so "
I think you're on to something! It certainly has to do with comparison to the literal "
Take care
Hey Leila
Thanks for this vdo.
just to understand, does putting "--" signs helps us, in getting formula sorted?
why not any other sign or number or any other thing, in replacement? can you enlighten me pls?
Take care 😇