Grab the file I used in the video from here 👉 pages.xelplus.com/count-text-file
@LeilaGharani
4 жыл бұрын
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 >.
@mohamed.montaser
4 жыл бұрын
can you make a video about this solution explaining it?
@drsteele4749
4 жыл бұрын
Notice that using COUNTIF(range,">"&""&"!") you will count the text cells in the range. Thus we see that COUNTIF(range,">
@chronicle5553
4 жыл бұрын
COUNTIF(range,">" is Comparison operator and "
@NVenkadesh
4 жыл бұрын
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.
@nathansoori
4 жыл бұрын
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.
@ExcelExciting
4 жыл бұрын
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..
@ReactionSquad158
4 жыл бұрын
Appclause really i appreciate ur hardship... Worth watching
@LeilaGharani
4 жыл бұрын
I'm glad you like it Muhammad.
@ReactionSquad158
4 жыл бұрын
@@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
@Prabasmsoffice
4 жыл бұрын
As usual, excellent video.
@vijaysahal4556
4 жыл бұрын
superb madam 👍👍👍👍👍👍👍👍👍
@anjinoureddine1513
4 жыл бұрын
Thank you liela for all these usefull formula
@realtoast7036
4 жыл бұрын
Excellent. I can use this.
@AmitVerma-vk4sj
2 жыл бұрын
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
@christianandreassen7990
4 жыл бұрын
You've done it again - excellent video !
@TomazTacla
4 жыл бұрын
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?
@salauddinkhokon5596
4 жыл бұрын
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
@pipse81
4 жыл бұрын
we can used also SUMPRODUCT(--ISTEXT(F15:F20))
@davidhansen527
4 жыл бұрын
Could this be version dependent? does not seem to be working on 2016
@PK-Shahxad
4 жыл бұрын
Can you please send me an excel book where mostly used/needs formula you have compile please
@excelisfun
4 жыл бұрын
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 ">
@LeilaGharani
4 жыл бұрын
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.
@excelisfun
4 жыл бұрын
@@LeilaGharani The internal Excel engine sort does crazy things again. That is really amazing!!!!
@Carla-ei6ew
Жыл бұрын
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 😎
@rldb
4 жыл бұрын
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).
@TheBOULDER666
3 жыл бұрын
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,"*
@oriyomishakirudeen3285
3 жыл бұрын
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
@TomaszKobza
4 жыл бұрын
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.
@TomaszKobza
4 жыл бұрын
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.
@tiborszekeres5798
4 ай бұрын
@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... :)
@deepashankar08
3 жыл бұрын
If Mr Excel and Leila dont know the answer to something in Excel, I very much doubt anyone else would!! Great job as always!
@gururajpatil4158
3 жыл бұрын
hi, leila. I have multiple numbers in column as the register numbers, like 1902901,1821820,172918291,19012810,18102801. all mixed together..etc.. first two digits are basically represent year. How do i count cells with 19, 18 and 17 so on?? As of now i am extrcting left(text,2) and making a column, then COUNTIF(Colums!$K$1:$K$36,Ref cell) I am looking for clubbing of these two formula in one, thanks help is much appreciated in advance.
@AshokKumar-sy2qt
4 жыл бұрын
Liked it 👍
@ublogs
3 жыл бұрын
A1=SUBJECT A2=MATH A3=ENGLISH A4=MUSIC A5=DRAWING B1=GRADING B2=NO B3=NO B4=YES B5=YES C1=MARKS C2=100 C3=80 C4=50 C5=30 ABOVE IS DATA OF TABLE ONE. AND THIS DATA IS CONVERTED INTO TABLE BY PRESSING CTRL.+T NOW WHAT IS REQUIRED IS TO CREATE A NEW TABLE WITH SUBJECTS WHICH ARE GRADING SUBJECTS ONLY. [YES] F1=SUBJECT F2=MUSIC F3=DRAWING G1=MARKS G2=50 G3=30 ONE LAST THING IS TO ADD MARKS OF NON GRADING SUBJECT VIA FORMULA.
@ToddBeal
Жыл бұрын
@Leila Gharani | ">
@mohammedyusufkhan2571
Жыл бұрын
Hi Is there any formula to calculate the Specific word just like Toyota, BMW, Mazda Etc. But excluding the Hidden Rows, Just like if we want to get the column total but only which are visible so we use the formula =SUBTOTAL, same we need to calculate the specific text, Please let me know if you can help me. Thanks
@sunilpinto8428
6 ай бұрын
Nice video, I have different kind of requirements: my data contains numbers, text, numbers with formula, text with formula. I need count of numbers without formula and count of text without formula can anybody know the formuls
@peterbu27
4 жыл бұрын
>< = "IS NOT NUL". numbers are not strings so are null strings. Your ISBLANK in D9 returnsa NUL ( "" ) value.
@Franmeliv
Жыл бұрын
Hi! What if I want to count how many times the word "game" appears in each row? For example: game blank blank 1 blank game blank 1 blank blank blank 0 blank blank game 1 Something like what is shown above. Please help me. Thank you very much.
@dbmnfinity1
2 жыл бұрын
I don't know if this question has been asked, how can you count name in excel.....ex: I have 200 reps and I need to track how many survey's they have completed. John has 47 , Jane has 20 and Paul has 78. there name will be entered individually..... how can I create a running total of them please.
@k.rameshkumar2482
4 жыл бұрын
How to remove automatically zero quantity items details using formula example:- Apple quantity is 0
@prakashbhaisuthar621
2 жыл бұрын
Madam, what is formula for counting unique value of different cell? for example In A1 cell has the text 'teacher' C1 cell has the text 'teacher' E1 Cell has the text 'teacher but In B1, D1 has not the text 'teacher'. how can we use formula for counting A1+C1+E1
@dmevasudevcreation7891
4 жыл бұрын
i want this count value in Sap-rated cell if D3:D5 is fill with text d3:D5=3 this count 3 value in d3 cell only , then D6:D8 if blank then fill with d6=0 this count value 0 in d6 cell only, if D9:D11 if fill with text D9:D17=9 this count 9 value in d9 cell only, cell references should be logical. please help me into this
@raghavdayal
4 жыл бұрын
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.
@johnwoods6910
5 ай бұрын
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.
@mohankumar-qy8uc
3 жыл бұрын
What if I have a criteria from B col i.e Division.Then how to count cell from Col c having criteria from col B Suggest Thanks
@naimahersy3966
2 жыл бұрын
How to insert a COUNTIF function to count values once it reaches to the first zero value to stop. And count again the cells between the first zero and second zero. any help will be appreciated
@KarolKarasiewicz
2 жыл бұрын
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...
@JV-xg5jk
4 жыл бұрын
Can someone pls explain why "?*" works for text but not number??
@Chris-op7yt
4 жыл бұрын
Spreadsheets are a terrible choice for doing anything with text. case in point an empty string not being same as empty cell. Unless you test thoroughly these formulae, you may get undesired results in the future. for example, what if the cell contains a date or UTF-8 character, etc. Best to avoid overreach in processing text by using a spreadsheet that regularly auto-converts contents via a guessing game.
@tapomoychakraborty2880
3 жыл бұрын
I have a column with Yes or No. I want to count all "No" from beginning and it should stop counting when it reaches first Yes. Please help
@sajidhkabeer3634
Жыл бұрын
Thank you very much, today I learned to calculate things in report.
@mamatalksrealfun6822
3 жыл бұрын
Lilie please I need to know with which formula to use for count if cell A is empty and B is 10hrs and C is 9hrsand D 11hrs and E is empty My question is that how do I count total time Lesser than 10hrs and greater than 10hrs
@susanmiller9410
Жыл бұрын
I am trying to count how many positions remain after being filled. In one column I have the number of positions e.g. 10 in the other column I have the name of the person who filled the position. I want to position number to change automatically. Can this be done?
@djc1309
2 жыл бұрын
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 ">
@sagarmathur3535
3 жыл бұрын
Pls explain every single step for layman people like me... like mathematiccal sign
@kaajyo
4 жыл бұрын
Never seen. I think it's interpreted as greater then "
@LeilaGharani
4 жыл бұрын
That's what one of our team members was on to - but there are many characters with ASCII that come after 60 that is also not "seen" by the syntax. For example tilde has code of 126 and isn't seen by the operator. Also the strange one is = and > are seen but < isn't.
@igorbaca
4 жыл бұрын
@@LeilaGharani I just tried this, it does not 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.
@SolidSnake59
4 жыл бұрын
@@igorbaca Yes, I have the similar results. It must use alphabetic order, but without spaces of any kind.
@LeilaGharani
4 жыл бұрын
@@igorbaca You got it! I tested this up to Char 255 and your theory holds. I sorted the hard-coded characters (after converting numbers) and it's based on the sorting order of the resulting characters and not the code. Tilde is before > and all the usual alphabetic characters are after the >. Thank you!
@JDL891
4 жыл бұрын
@@igorbaca Sorry what do mean by converting numeric characters to numbers? I thought numeric characters are numbers
@kaymr82
2 жыл бұрын
But how do you count cells with text that include some cells that are blank or have other texts i dont want to include? I am trying to add cells with specific texts. please help
@pedjanbgd4221
4 жыл бұрын
Wow Leila, I never knew ">
@Roy-tf7fe
Жыл бұрын
Ahh, forgot something. I wonder if it would be useful in other functions?
@sarathp4253
2 жыл бұрын
Is there an addition to this formula for avoiding duplicate texts?
@usamamehmood6243
3 жыл бұрын
MAM, HOPE YOU ARE DOING WELL, I HAVE ONE CONFUSION, IN COUNTA THEY COUNT HEADER ALSO, SO HOW WE COUNT ARE DETAIL WITHOUT COUNT HEADER
@abuhanif5011
Жыл бұрын
What if when I filter some rows. Is the result remaining same or change the value?
@NamanArya
4 жыл бұрын
Leila Gharani's Excel is now powered by Demogorgan (Stranger Things) hahaha
@LeilaGharani
4 жыл бұрын
Very scary stuff :)
@NamanArya
4 жыл бұрын
@@LeilaGharani maybe you can use the TRIM formula on them 🤣😂
@cbsubs
4 жыл бұрын
well, I have been using this: countifs(D4:D13,""&"")
@DaniyalGamerGuy
4 жыл бұрын
this will however count the total number of cells including blank
@nigeljohnson2687
Жыл бұрын
Hi Leila, How do you count text cells that are not equal to a text and ignore blank cells please
@sankarannarayanan2781
4 жыл бұрын
Different information Not equal to is the symbol .!
@erwinfscortez6013
Жыл бұрын
Thank you. It solves my problem.
@asdfghjk1992
2 жыл бұрын
Thank you so much. Finally, have the solution.
@camslazaro8271
2 жыл бұрын
How do you count the number of gaps in a sequence number?
@aadeshr507
Жыл бұрын
Thanks a lot it helped me count ifs formula as well!!
@blaiseganguin4710
2 жыл бұрын
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!
@sathyam1968
4 жыл бұрын
If the data in the range starts with anything other than alphabets or numbers and is a string then this does not work. the text in the cell should start with a number or an alphabet to be considered
@iawwad
4 жыл бұрын
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
@abirirosen
4 жыл бұрын
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.
@makisalimhussain2334
4 жыл бұрын
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,">
@lisasingh6329
2 жыл бұрын
Very helpful. Thank you.
@makisalimhussain2334
2 жыл бұрын
@@lisasingh6329 You are welcome
@garyipo4891
2 ай бұрын
Leila, you are a genius. My problem is solved. Thanks alot..
@LeilaGharani
2 ай бұрын
Happy to help!
@lougilberts135
Жыл бұрын
How do I count occurrence of a word from a group words in a cell?
@varanasikishore8884
7 ай бұрын
Anyone know How to count only alternating cells ?
@214Wildbill
Жыл бұрын
How many If(or statements can there be, only 2??? Or can there be more, just wonder, I've had all kinds of trouble with it...
@LeilaGharani
Жыл бұрын
You can nest up to 64 different IF functions although I don't recommend trying that :)
@dejabluek8297
4 жыл бұрын
I have needed =COUNTIF(range,"") for a while now. You have saved me from so many future headaches. Thank you!!
@qred1970
4 жыл бұрын
What if you are looking for a range of numbers such as
@deepashankar08
3 жыл бұрын
You can use either =SUMPRODUCT(--(Range,
@philliesfaninlyon
4 ай бұрын
How to count like you do at 0:45? Thanks
@mohammedfaizan.v3783
4 жыл бұрын
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 ☺️
@redhaakhund1271
2 жыл бұрын
Excellent, thanks a lot 👍👍👍👍👍
@daXcel7448
4 жыл бұрын
={SUM(--ISTEXT(F5:F12))}
@rajkirandhyani6029
4 жыл бұрын
>
@thangaveluraj5366
2 жыл бұрын
Hv you cracked this yet?
@apn6824
4 жыл бұрын
It's confusing a little bit. Need more clarity on this..God bless you.
@alancowlishaw7854
4 жыл бұрын
This isn’t new. It was used in 2011
@Chaouamahdi
4 жыл бұрын
Sorry bb i don’t know 🤷♂️
@habukproductions6966
4 жыл бұрын
I think It is greater than ASCII 60
@dinethprabash1001
3 жыл бұрын
excel must be thinking you're angry..
@LeilaGharani
2 жыл бұрын
😂
@muhamadnaseemganji651
Жыл бұрын
This means anything that is not null
@tonyfreelance4519
4 жыл бұрын
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 "
@benmega4379
Жыл бұрын
I think you're on to something! It certainly has to do with comparison to the literal "
@AmitKumar-gx8fy
3 жыл бұрын
Hi I have a list of clients with their project names, i want to count the clients but some of clients have more than two projects so their names repeated, what formula should I use to count repeated names only once.
@deepashankar08
3 жыл бұрын
Combine Counta and Unique, so it be Counta(Unique(Columnwithduplicatevalues))
@mohamedchakroun4973
4 жыл бұрын
Great Stranger Tip for the first time i see >< Thumbs up
@mireillecantrell4638
4 жыл бұрын
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!
@LeilaGharani
4 жыл бұрын
Wow, thank you, Mireille!
@mohammadashiq9996
4 жыл бұрын
find cell with the hidden value
@michalroesler
3 жыл бұрын
What a hot formula this is...
@h4niali
Жыл бұрын
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? 😊
@214Wildbill
Жыл бұрын
Thank you Leila
@sandy20654
4 жыл бұрын
I love the knowledge you share in your videos. Thank you. I've learned so much over these last months.
@عبدالقادرعبدالقادر-ف7ج
4 жыл бұрын
Great leila , see here the formula =COUNTIF(C5:C16,"
@siubrown3448
4 жыл бұрын
Hello, I have discovered that =Countif works for numbers and =CountA works for text.
@aravinthanvs8060
3 жыл бұрын
Thank you so much
@Rythx
3 жыл бұрын
It's helpful!
@wayneedmondson1065
3 жыл бұрын
Hi Leila. An awesome and interesting tip! Thanks for sharing :)) Thumbs up!!
Пікірлер: 312