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.
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 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!
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.
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😋
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 ☺️
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 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..
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?
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.
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.
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.
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.
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 ">
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.
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).
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
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,"*
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 "
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?
@@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
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
@@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]>""
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.
@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. The reason the formula works is because the criteria argument in the COUNTIF function treats "" as wildcard characters that match any text string that contains those characters. Therefore, the formula will count all cells that contain any text string. Oh, I found this out with the help of CHAT GPT 😎
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. 🥺
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.
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.
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 spent almost two hours to resolve this small but mighty issue but I thought of checking your page out. This really help, many thanks
I love the knowledge you share in your videos. Thank you. I've learned so much over these last months.
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!
I have needed =COUNTIF(range,"") for a while now. You have saved me from so many future headaches. Thank you!!
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!
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!!!!
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!
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.
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 ☺️
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 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.
Thank you! This is just the formula I needed today! Keeping sharing!
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..
I had come across "" before and it confused me a bit. I hadn't come across ">
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.
Das ist so cool, Leila Ich habe noch nichts von dieser Syntax gehört. Viele liebe Grüße 🌞
This is an awesome hack....I like this type...please post more of this kind.
I have tested that formula in Apple’s Numbers has well and it works too! Interesting hack.
Thanks a lot it helped me count ifs formula as well!!
You've done it again - excellent video !
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?
Hi Leila. An awesome and interesting tip! Thanks for sharing :)) Thumbs up!!
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 :)
in fact i needed today but i had to use long formula waw great to learn this thank you
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!
As usual, excellent video.
Thank you. It solves my problem.
you are awesome i just solved a question related to this formula, thank u
brilliant trick.. please keep sharing.
Thank you very much, today I learned to calculate things in report.
Amazing! As usual 😊 thanks alot
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.
You are really gem of Excel.. Kudos...
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,">
Till date ur each n every video has always been thumps up.
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
I have never seen this!!!! Thanks Leila
Excellent, thanks a lot 👍👍👍👍👍
Thank you so much. Finally, have the solution.
Perfect, I've been looking for this exact formula... thanks!
Happy to help!
@@LeilaGharani =">
Well done, good explanation.
THANK YOU SO MUCH FOR THIS VIDEO. THIS SAVED ME
Cool . These all syntax are new to me 😊
Your skills are really amazing.
Thank you!
Could you please make a video of OLAP, Pivot Table?
Nice. You are simply amazing
Thank you liela for all these usefull formula
Wonderful as always
this is great! thank you
Wow Leila, I never knew ">
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 ">
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.
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).
Amazing video well done 😊😊❤️❤️👍👍
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
great mentor A huge respect from Pakistan
Thank you so much
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,"*
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 "
Excellent. I can use this.
it was superb ms liela I want to learn advance excel nd vba with you
Thanks you sister. You are brilliant girl.
Just awesome!
very good tip!!!
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?
U r genius. Really amazing
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
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
It's helpful!
I really searching for a formula as same. Got it. Wow....
Thank you Leila
one I will remember!! good discovery
Great Stranger Tip for the first time i see >< Thumbs up
Thank you 🙏
Great leila , see here the formula =COUNTIF(C5:C16,"
Thanks Leila.
Thats a good trick, I need to use that sum product feature though on filtered data !!
superb thank you
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 a lot
Beautiful
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.
@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. The reason the formula works is because the criteria argument in the COUNTIF function treats "" as wildcard characters that match any text string that contains those characters. Therefore, the formula will count all cells that contain any text string.
Oh, I found this out with the help of CHAT GPT 😎
Thanks
Very useful
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.
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.
Thanks.
superb madam 👍👍👍👍👍👍👍👍👍
What a hot formula this is...
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
Hi? As you've explained I have tried to play around with the strange formula its just counting cells with texts
Liked it 👍
thanks.....
I believe it's something like "bot equal" in text. If You type aby text between > and < it ahould count all texts mit equal to this text...