Forum Discussion
formula
I need the formula to find how many times with in a certain amount of cells that neither two words show up. Usually you use a COUNTIF function to find how many times a word shows up but im not sure how to find if two words don't show up.
Hi Morgan,
Not sure I understood you correctly. If you have one word (not string) in each cell and would like to know if any of them is in the range of cells not more than one time, you may
- calculated number of unique words in the range (e.g. https://exceljet.net/formula/count-unique-values-in-a-range-with-countif) and compare with range size if they are equal or not.
I have a bunch of fruit names for example: orange, banana, pear, and apple. I need to know the function for it saying neither "Apple" or "Orange" within those cells.
Like this maybe?
=COUNTA(B2:B10)-SUM(COUNTIFS(B2:B10,{"*apple*","*banana*"}))
In words: count the cells that have either the word "apple" OR the word "banana" and subtract the result from the total count of the cells.
