How to Count Cells With Text in Excel (Mysterious Formula as NEVER Seen Before)

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...

Thank you Leila

Thank you very much, today I learned to calculate things in report.

I see. In this case, the symbol >< is used as a text value, not as an operator. the criterion is "><", which means you are counting the number of cells in D3:D14 that contain the exact text "><". For example, if cell D3 has the text "a><b", it will be counted, but if cell D2 has the text "a>b", it will not be counted. Does this make sense? 😊

How do I count occurrence of a word from a group words in a cell?

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

Ahh, forgot something. I wonder if it would be useful in other functions?

You just saved my life

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?

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.

Thanks a lot it helped me count ifs formula as well!!

This means anything that is not null

Hi. The reason the formula works is because the criteria argument in the COUNTIF function treats "<" and ">" 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 😎

What if when I filter some rows. Is the result remaining same or change the value?

Is there a formula that makes me count words only within a group of cells? even if the cells contains a sentence

Thank you. It solves my problem.

Hi Leila, How do you count text cells that are not equal to a text and ignore blank cells please

@Leila Gharani | "><" criteria tests for real Text, not "" zero length strings.
The following formula returns a count of 1 for each occurence of real text: =COUNTIF(C5:C7,"><"). It only looks for actual text: not numbers, not empty cells, not zero length text strings - real text only.

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!

How do you count the number of gaps in a sequence number?

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...

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

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.

Thank you so much. Finally, have the solution.

SUPPOSE I AM A POLICE OFFICER. I FIND A MOBILE PHONE OF A VITIM. I WANT TO KNOW WHICH NUMBER/NUMBERS CALLED THE VICTIM MMOST OR HOW MANY TIMES WHICH NUMBERS CALLED THE VICTIM MOST IN LAST WEEK BEFORE THE VICTIM FOUND DEAD.

Thank you! This is just the formula I needed today! Keeping sharing!

Excellent, thanks a lot 👍👍👍👍👍

Perfect, I've been looking for this exact formula... thanks!

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

We can use formula counta - count

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

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 "><" as "Greater Than or Less Than"

Is there an addition to this formula for avoiding duplicate texts?

Hv you cracked this yet?

Thanks

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

excel must be thinking you're angry..

Thank you so much

Hi Leila. An awesome and interesting tip! Thanks for sharing :)) Thumbs up!!

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. 🥺

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 are awesome i just solved a question related to this formula, thank u

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

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

It's helpful!

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

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,"*<6 Months*")
Here it will only count cells displaying |<6 Months|
And ignore hidden formulas, even if the formulas contain the same text. Only what is displayed gets counted.