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 >.
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.
This powerful little formula solved my issue of excluding formulas in a column of text and formulas. I only wanted to count the number of cells with text, not cells with formulas or blank cells. I was planning to reach out to a Data Programmer to solve this issue. Now I don't have to, plus your Count Formula saved me about $125 in fees which the Data Programmer would have charge me. Thank you so much for posting this Count Formula.
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!
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.
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!
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😋
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
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 ☺️
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..
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.
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!
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 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.
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.
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?
@@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
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 ">
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,">
@LeilaGharani - I would like to ask you, that are there any way to use the COUNTIF formula in your example workbook area of D:D - with the criteria of "Arcade"? So I want to achieve that the COUNTIF counts the cells in a column which are containing a specific keyword but in that column already a formula working and the results coming from that formula. In you example that is the IF, but I am using VLOOKUP in many cases and I want to have the counts of the specific results of that VLOOKUP... :)
Hi, Leila. I'm trying to use COUNTA to count a range per unique criteria but per checking it returns a value when it shouldn't. Also used UNIQUE and FILTER in the formula string. Hope you can explore. 🥺
It's just cells with content bigger (first ">") than second char "Arcade" and see the result. :-) The best condition will be ">= " (with space) or "> " depends of expectations I think.
I checked the results and "> " (with space) include numbers as text and some special chars. But it counts two spaces :-) so it is seems than it is better to try ">=!" but it doesn't include texts starting with spaces like " !". Ascii order of chars is "!"#$%&'()*+,-./0123456789:;?@ABC...." so we have to decide what results we want to get. It is very interesting but clearly not so perfect solution.
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?
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,"*
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
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.
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
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).
Hi Leila, Interesting one. I do have a question though. It seems the formula (using either 'len' or '') seems to be not working when we there is a 'sum' formula in the empty cell. Do you know any reasons why? Thanks Ravi
If the SUM result is 0 and the 0 is hidden with custom formatting, then it will still count it.... You can check if you go to the cell and apply the general format. Do you see the 0? To avoid counting zeros you could add that as criteria to the SUMPRODUCT formula.
@@konstantinevseyenko9312 yes you're right it's not exact, but it seems to similar. After your comment I did CHAR(1) to CHAR(128), converted text to numbers and tested =[cell reference]>""
Thanks alot leila Question please.. If we have a range a1:a20 Some cells have one country name Some cells have two or three country names . How to count number of unique countries in the range without using helper columns Ex , A2 = USA , A5 = CANADA , KOREA , A8 = KOREA , USA ,SWEDEN
Leila, I've tried so many options to solve this issue and failed miserably. I click on your page and there is the "Mysterious Formula" to solve the issue. Thank you so much.
Hi Leila, Is there any way of converting multiple timezones in excel. Like I want to convert USA, England, Brazil, Cuba, Austrialia, China, Russia, Iran, India time into Singaporean timezone with date. Basically I have online customer chat meetings from multiple countries at specific time & date and want to convert them into Singaporean timezone. Can you help in this regards.
Grab the file I used in the video from here 👉 pages.xelplus.com/count-text-file
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
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!!!!
This powerful little formula solved my issue of excluding formulas in a column of text and formulas. I only wanted to count the number of cells with text, not cells with formulas or blank cells. I was planning to reach out to a Data Programmer to solve this issue. Now I don't have to, plus your Count Formula saved me about $125 in fees which the Data Programmer would have charge me. Thank you so much for posting this Count Formula.
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!
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!
If Mr Excel and Leila dont know the answer to something in Excel, I very much doubt anyone else would!! Great job as always!
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!
I have needed =COUNTIF(range,"") for a while now. You have saved me from so many future headaches. Thank you!!
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.
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.
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 ☺️
brilliant trick.. please keep 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.
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..
Leila, you are a genius. My problem is solved. Thanks alot..
Happy to help!
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 :)
You are really gem of Excel.. Kudos...
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.
great mentor A huge respect from Pakistan
Thank you! You're such a great teacher.
I'm from Colombia and learning about excell.
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 "
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
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.
As usual, excellent video.
Thank you! This is just the formula I needed today! Keeping sharing!
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?
I have tested that formula in Apple’s Numbers has well and it works too! Interesting hack.
Hi Leila. An awesome and interesting tip! Thanks for sharing :)) Thumbs up!!
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
This is an awesome hack....I like this type...please post more of this kind.
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 ">
Your skills are really amazing.
this lady is awesome
I had come across "" before and it confused me a bit. I hadn't come across ">
you are awesome i just solved a question related to this formula, thank u
Perfect, I've been looking for this exact formula... thanks!
Happy to help!
@@LeilaGharani =">
You've done it again - excellent video !
I have never seen this!!!! Thanks Leila
Thank you. It solves my problem.
Excellent, thanks a lot 👍👍👍👍👍
Well done, good explanation.
Wonderful as always
Das ist so cool, Leila Ich habe noch nichts von dieser Syntax gehört. Viele liebe Grüße 🌞
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,">
Very helpful. Thank you.
@@lisasingh6329 You are welcome
Thank you so much. Finally, have the solution.
Thank you!
Could you please make a video of OLAP, Pivot Table?
Till date ur each n every video has always been thumps up.
Thanks a lot it helped me count ifs formula as well!!
@LeilaGharani - I would like to ask you, that are there any way to use the COUNTIF formula in your example workbook area of D:D - with the criteria of "Arcade"?
So I want to achieve that the COUNTIF counts the cells in a column which are containing a specific keyword but in that column already a formula working and the results coming from that formula. In you example that is the IF, but I am using VLOOKUP in many cases and I want to have the counts of the specific results of that VLOOKUP... :)
THANK YOU SO MUCH FOR THIS VIDEO. THIS SAVED ME
Hi, Leila. I'm trying to use COUNTA to count a range per unique criteria but per checking it returns a value when it shouldn't. Also used UNIQUE and FILTER in the formula string. Hope you can explore. 🥺
You have to be a bit more specific about it if you're looking for answers, this seems very vague.
Thanks you sister. You are brilliant girl.
It's just cells with content bigger (first ">") than second char "Arcade" and see the result. :-) The best condition will be ">= " (with space) or "> " depends of expectations I think.
I checked the results and "> " (with space) include numbers as text and some special chars. But it counts two spaces :-) so it is seems than it is better to try ">=!" but it doesn't include texts starting with spaces like " !".
Ascii order of chars is "!"#$%&'()*+,-./0123456789:;?@ABC...." so we have to decide what results we want to get. It is very interesting but clearly not so perfect solution.
Thank you very much, today I learned to calculate things in report.
Thank you liela for all these usefull formula
Excellent. I can use this.
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?
superb madam 👍👍👍👍👍👍👍👍👍
How to count like you do at 0:45? Thanks
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,"*
Great leila , see here the formula =COUNTIF(C5:C16,"
in fact i needed today but i had to use long formula waw great to learn this thank you
Great Stranger Tip for the first time i see >< Thumbs up
Cool . These all syntax are new to me 😊
Amazing! As usual 😊 thanks alot
it was superb ms liela I want to learn advance excel nd vba with you
U r genius. Really amazing
Liked it 👍
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
Thank you so much
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.
What a hot formula this is...
Thank you Leila
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
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).
Nice. You are simply amazing
very good tip!!!
It's helpful!
superb thank you
Amazing video well done 😊😊❤️❤️👍👍
Hi Leila,
Interesting one. I do have a question though. It seems the formula (using either 'len' or '') seems to be not working when we there is a 'sum' formula in the empty cell. Do you know any reasons why?
Thanks
Ravi
If the SUM result is 0 and the 0 is hidden with custom formatting, then it will still count it.... You can check if you go to the cell and apply the general format. Do you see the 0? To avoid counting zeros you could add that as criteria to the SUMPRODUCT formula.
@@LeilaGharani Can you give the formula for that, please?
I agree it interprets ">
Not this way, as no direct relation between code number and how this criteria filters
@@konstantinevseyenko9312 yes you're right it's not exact, but it seems to similar. After your comment I did CHAR(1) to CHAR(128), converted text to numbers and tested =[cell reference]>""
Thanks alot leila
Question please..
If we have a range a1:a20
Some cells have one country name
Some cells have two or three country names .
How to count number of unique countries in the range without using helper columns
Ex , A2 = USA
, A5 = CANADA , KOREA
, A8 = KOREA , USA ,SWEDEN
Wow Leila, I never knew ">
Leila, I've tried so many options to solve this issue and failed miserably. I click on your page and there is the "Mysterious Formula" to solve the issue. Thank you so much.
Is there an addition to this formula for avoiding duplicate texts?
I see. In this case, the symbol >< is used as a text value, not as an operator. the criterion is ">b", it will not be counted. Does this make sense? 😊
Can u tell how we do same thing with subtotal. I mean to say ignore the fomula based empty cell like u did in this video with subtotal function
My favorite moment happens at 02:37. So cute 😍
this is great! thank you
I've used it many times, but more out of accident / trial and error as I keep forgetting if it is or >
Thanks for sharing!
This is Very Usefull trick but m still have a question in my mind that how to total count text value when we have Game in B coloum
get well soon
Hi Leila, How do you count text cells that are not equal to a text and ignore blank cells please
Hi Leila, Is there any way of converting multiple timezones in excel. Like I want to convert USA, England, Brazil, Cuba, Austrialia, China, Russia, Iran, India time into Singaporean timezone with date. Basically I have online customer chat meetings from multiple countries at specific time & date and want to convert them into Singaporean timezone. Can you help in this regards.
thanks a lot
I really searching for a formula as same. Got it. Wow....